Have I to count transactions before rollback one in catch block in T-SQL?

Posted by abatishchev on Stack Overflow See other posts from Stack Overflow or by abatishchev
Published on 2010-05-09T10:07:57Z Indexed on 2010/05/09 10:18 UTC
Read the original article Hit count: 243

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

?

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql-server-2008