問題描述

在 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;

重點在

1. 需要判斷當交易已經開啟時，使用 SAVE 指令。
2. 執行 BEGIN , COMMIT, ROLLBACK 指令時均指定 Transaction 名稱，COMMIT 與 ROLLBACK 是否要執行得視交易當下的狀態。

 

參考資源

SQL Server 2008 error handling best practice