2

I have next block in the end of each my stored procedure for SQL Server 2008

BEGIN TRY
    BEGIN TRAN
        -- my code
    COMMIT
END TRY
BEGIN CATCH
    IF (@@trancount > 0)
    BEGIN
        ROLLBACK
        DECLARE @message NVARCHAR(MAX)
        DECLARE @state INT
        SELECT @message = ERROR_MESSAGE(), @state = ERROR_STATE()
        RAISERROR (@message, 11, @state)
    END
END CATCH

Is it possible to switch CATCH-block to

BEGIN CATCH
    ROLLBACK
    DECLARE @message NVARCHAR(MAX)
    DECLARE @state INT
    SELECT @message = ERROR_MESSAGE(), @state = ERROR_STATE()
    RAISERROR (@message, 11, @state)
END CATCH

or just

BEGIN CATCH
    ROLLBACK
END CATCH

?

gbn
  • 422,506
  • 82
  • 585
  • 676
abatishchev
  • 98,240
  • 88
  • 296
  • 433

2 Answers2

4

Actually, I never start a new transaction if I'm already in one.

This deals with nested stored procs, distributed TXNs and TransactionScope

Remember, there is no such thing as a nested transaction in SQL Server anyway. 

DECLARE @StartTranCount int

BEGIN TRY
    SET @StartTranCount = @@TRANCOUNT
    IF @StartTranCount = 0 BEGIN TRAN
        -- my code
    IF @StartTranCount = 0 COMMIT TRAN
END TRY
BEGIN CATCH
    IF @StartTranCount = 0 AND @@trancount > 0
    BEGIN
        ROLLBACK TRAN
        DECLARE @message NVARCHAR(MAX)
        DECLARE @state INT
        SELECT @message = ERROR_MESSAGE(), @state = ERROR_STATE()
        RAISERROR (@message, 11, @state)
    END
    /*
    or just
    IF @StartTranCount = 0 AND @@trancount  
        ROLLBACK TRAN
    */
END CATCH
Pearce Grinnell
  • 28
  • 5
gbn
  • 422,506
  • 82
  • 585
  • 676
  • I mean that I always start a transaction (and try-block) in the beginning of each my DML stored procedure. So my question is - have I to check @@trancount in appropriate catch-block? – abatishchev May 09 '10 at 11:18
  • @abatishchev: yes: it may already have rolled bck (eg in a trigger) – gbn May 09 '10 at 12:01
2

You need to check that there is a transaction in scope before trying to rollback.

You can use the following:

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

This will rollback the transaction, but no error will be reported back to your application.

Check MSDN for more info.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • What if I'm always inside a transaction scope? See my edited post – abatishchev May 09 '10 at 10:15
  • 1
    If you _know_ that you are within a transaction, then the only statement you will need is the `ROLLBACK` statement. You will still not get any error reported to your app. – Oded May 09 '10 at 10:46