SQL SERVER 交易處理（一）

 

1. 一個預存程序最好只有一個事務；多個事務既消耗資源，也容易產生錯誤；

2. 建立事務和結束事務的時候最好有這樣的判斷：

Code
IF @@TRANCOUNT = 0
BEGIN -- only if @@TRANCOUNT is 0, we do BEGIN TRAN
    BEGIN TRANSACTION
    SET @TransactionIsOurs = 1
END        
IF @TransactionIsOurs = 1
BEGIN
    COMMIT TRANSACTION
    -- SET @TransactionIsOurs = 0
END
IF @TransactionIsOurs = 1 AND @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

 

3.  如果事務很長，可以在適當的位置多加幾個錯誤判斷：

Code
IF @@Error <> 0
BEGIN
   RAISERROR (50002, 16, 1, N'ApplyCheckedObjectsStage', N'spApplyCheckedObjectsStage', 10, N'Checked Agence Stage', @StrErrorParm) WITH LOG
   SET @ReturnCode = -1000
   GOTO exit_label
END

 

     Note：@@Error必須直接跟在需要判斷的sql後面，中間不能間隔其他語句，因為@@Error總是取最新執行的結果，所以最好還是使用Try...Catch...

Code
USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
        WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO