CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Kuyenda
Kuyenda

Reputation: 4609

Is it possible to rollback a SQL Server transaction without suppressing the error?

This StackOverflow answer provides an elegant way to use TRY CATCH ROLLBACK TRANSACTION, but it suppresses the error information.

Is there any way to rollback a transaction that doesn't suppress the error that resulted in the rollback in the first place?

Here's an example T-SQL script based on the above method:

DECLARE @Table TABLE ( ID INT PRIMARY KEY )

BEGIN TRY

    BEGIN TRANSACTION

        INSERT INTO @Table VALUES (1), (1)

    COMMIT TRANSACTION

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION

END CATCH;

Thanks!

Upvotes: 3

Views: 2538

Answers (2)

Kuyenda
Kuyenda

Reputation: 4609

For posterity, I'm including the equivalent of THROW for SQL Server 2008R here.

This is from the section labeled "Returning error information from a CATCH block" at the following page:

http://technet.microsoft.com/en-us/library/ms178592(v=sql.105).aspx

BEGIN TRY
    -- RAISERROR with severity 11-19 will cause execution to 
    -- jump to the CATCH block.
    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

Upvotes: 1

ChrisLively
ChrisLively

Reputation: 88044

In the Catch statement get the error code and message. Then simply RAISE it again. The following shows a few examples.

http://technet.microsoft.com/en-us/library/ms175976.aspx

(per RBarryYoung in the comments)

In the CATCH clause, just use THROW

Upvotes: 5

Related Questions