2.txt

存储过程

USE [wenjian]
GO
/****** 对象:  Table [dbo].[student]    脚本日期: 02/26/2008 23:55:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[student](
	[studentid] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[studentname] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[studentpass] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
 CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED 
(
	[studentid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF




declare @teacherid varchar(50),@studentid varchar(50),@studentname varchar(50),@return int
set @teacherid='001'
set @studentid='001' 
set @studentname='房俍'
exec studentinsert @teacherid,@studentid,@studentname,@return output
print @return


alter proc studentinsert @teacherid varchar(max),@studentid varchar(max),@studentname varchar(max),@return int output
as
declare @studentpassword varchar(max)
if not exists(select studentid from student where studentid=@studentid)
begin
begin TRANSACTION
begin try
insert into student values(@studentid,@studentname,'123456')
end try
begin catch
set @return=2
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
end catch
begin try
insert into studentteacher values(@studentid,@teacherid)
end try
begin catch
set @return=3
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
end catch
set @return=0
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
end
else
begin
set @return=1
end