存储过程和事务

       在存储过程中使用事务时非常重要的，使用数据可以保持数据的关联完整性，在Sql server存储过程中使用事务也很简单，我们先来简单了解一下存储过程和事务，然后用一个例子来简单说明他们的用法：

存储过程：

 

事务：

 

以学生注册信息为例：

Create procedure [dbo].[PROC_stuRegister]     --设置参数
    @cardNo varchar(10),@stuNo varchar(11),@cardType  varchar(8),@cardBalance decimal(18,1),@cardState varchar(20),@RegisterDate varchar(20),@RegisterTime varchar(20),@UserName varchar(20),
    @isCheckout char(2),@stuName varchar(20),@Sex varchar(2),@Department varchar(20),@Grade varchar(20),@stuClass varchar(20),@Remark varchar(20),
    @rechargeDate varchar(20),@rechargetime varchar(20),@rrechargeCash decimal(18,1)
AS
BEGIN
	--事务
		SET NOCOUNT ON;
		SET XACT_ABORT ON;
		Begin try
		begin Tran   --开始一个事务
--添加卡表
Insert into T_Cardinfo（cardNo,stuNo,cardType,cardBalance ,cardState ,RegisterDate ,RegisterTime ,cardRegistrator ,isCheckout ) values(@cardNo,@stuNo,@cardType,@cardBalance ,@cardState ,@RegisterDate ,@rechargetime ,@UserName ,@isCheckout )
--添加学生表
Insert into T_Stuinfo(stuNo,stuName,sex,Department,grade,stuClass,Remark ) values(@stuNo,@stuName,@sex,@department,@grade,@stuClass)
--添加充值表
Insert into T_RechargeRecord(cardNo,rechargeDate ,rechargeTime ,rechargeOperator ,rechargeCash ,isCheckOut ) values(@cardNo,@rechargeDate ,@rechargetime ,@UserName ,@rrechargeCash ,@isCheckout )
		
	commit tran   --提交事务	

          end try
		
	begin catch
		
		--在此不可以使用xact_state来判断是否有不可提交的事务
		
		--只可以使用@@Trancount来判断是否有还未提交的事务，未提交的事务未必
		
		--就是不可提交的事务，所以使用@@TranCount>0后就RollBack是不准确的
		
		if @@TranCount>0
		
		rollback tran;  --回滚事务
		
		end catch
END

说明：1 、使用存储过程执行事物，需要开启XACT_ABORT参数(默认值为Off)，将该参数设置为On，表示当执行事务时，如果出错,会将transcation设置为uncommittable状态，那么在语句块批处理结束后将回滚所有操作；如果该参数设置为Off，表示当执行事务时，如果出错，出错的语句将不会执行，其他正确的操作继续执行。

2、当SET NOCOUNT 为 ON 时，不返回计数（计数表示受 Transact-SQL 语句影响的行数，例如在Sql server查询分析器中执行一个delete操作后，下方窗口会提示（3）Rows Affected）。当   SET NOCOUNT 为 OFF 时，返回计数,我们应该在存储过程的头部加上SET NOCOUNT ON 这样的话，在退出存储过程的时候加上 SET NOCOUNT OFF这样的话，以达到优化存储过程的目的。

 

附：

 1.捕获错误的函数有很多，如下：

 ERROR_NUMBER() 返回错误号。

ERROR_SEVERITY() 返回严重性。

ERROR_STATE() 返回错误状态号。

ERROR_PROCEDURE() 返回出现错误的存储过程或触发器的名称。

ERROR_LINE() 返回导致错误的例程中的行号。

ERROR_MESSAGE() 返回错误消息的完整文本。该文本可包括任何可替换参数所提供的值，如长度、对象名或时间。

 2.   有些错误，如sql语句中的表名称输入错误，这是数据库引擎无法解析这个表名称时，所发生的错误在当前的try…catch语句中无法捕获，必须由外层调用该存储过程的地方使用try…catch来进行捕获。