預存程序和事務

       在預存程序中使用事務時非常重要的，使用資料可以保持資料的關聯完整性，在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)ASBEGIN--事務SET NOCOUNT ON;SET XACT_ABORT ON;Begin trybegin 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 trybegin catch--在此不可以使用xact_state來判斷是否有不可提交的事務--只可以使用@@Trancount來判斷是否有還未提交的事務，未提交的事務未必--就是不可提交的事務，所以使用@@TranCount>0後就RollBack是不準確的if @@TranCount>0rollback tran;  --復原事務end catchEND

說明：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來進行捕獲。