使用交易的預存程序撰寫範本
問題描述
在 SQL Server 中撰寫具有交易(Transaction)控制的預存程序，若考慮預存程序會被另一個預存程序或觸發呼叫，如上圖 ProcedureA 呼叫 ProcedureB，在兩個預存程序中均可能發生錯誤需要進行 Rollback Transaction，此時交易控制不能如下段就簡單
BEGIN TRY BEGIN TRANSACTION; -- Do Something COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; END CATCH;
解決方案
此時需要做一點手腳，以下這個範本適用在主要預存程序或被呼叫附屬預存程序，撰寫時不需要額外傳遞交易狀態等參數。
DECLARE @hasOuterTransaction bit = case when @@trancount > 0 then 1 else 0 end;
DECLARE @rollbackPoint nvarchar(32)=replace(convert(nchar(36), newid()), N'-', N'');
BEGIN TRY
IF @hasOuterTransaction = 1
BEGIN
SAVE TRANSACTION @rollbackPoint;
END
ELSE
BEGIN
BEGIN TRANSACTION @rollbackPoint;
END;
-- Do Something
IF @hasOuterTransaction = 0
BEGIN
COMMIT TRANSACTION @rollbackPoint;
END;
END TRY
BEGIN CATCH
IF xact_state() = 1
BEGIN
ROLLBACK TRANSACTION @rollbackPoint;
END;
DECLARE @error_message nvarchar(1000)=ERROR_MESSAGE();
THROW 51000, @error_message, 1;
END CATCH;
重點在
- 需要判斷當交易已經開啟時，使用 SAVE 指令。
- 執行 BEGIN , COMMIT, ROLLBACK 指令時均指定 Transaction 名稱，COMMIT 與 ROLLBACK 是否要執行得視交易當下的狀態。