sqlserver中创建包含事务的存储过程

 1 ALTER proc [dbo].[Proc_InsertStudent]
 2 @stuName nvarchar(50),@stuClassId int,@stuAge int
 3 as
 4 begin
 5 set nocount on  --on表示不返回计数
 6 set xact_abort on  --当执行事务时，如果出错,会将transcation设置为uncommittable状态
 7 
 8 begin try 
 9 declare @stuCountByName int;
10 select @stuCountByName=count(*) from Students where Name=@stuName;
11 
12 if(isnull(@stuName,‘‘)=‘‘)
13 begin
14 print(‘名字不能为空‘);
15 return;
16 end
17 
18 if(@stuCountByName>0)
19 begin
20 print(‘名字重复‘);
21 return
22 end
23 
24 begin tran  --开启事务
25 insert into Students(Name,ClassId,Age) values(@stuName,@stuClassId,@stuAge)
26 commit tran  --提交事务
27 
28 end try
29 
30 begin catch
31 if xact_state()=-1
32 rollback tran;  --回滚事务
33 select ERROR_NUMBER() as ErrorNumber;
34 select ERROR_MESSAGE() as ErrorMsg;
35 end catch
36 set xact_abort off;
37 end

1 CREATE TABLE [dbo].[Students](
2  [ID] [int] IDENTITY(1,1) NOT NULL primary key,
3  [Name] [nvarchar](50) NOT NULL,
4  [ClassId] [int] NOT NULL,
5  [Age] [int] NOT NULL,
6  [CreateTime] [datetime] NOT NULL
7 );