Is there an equivalent in T-SQL to C#'s "throw;" to re-throw exceptions?

Question

The title really is the question for this one: Is there an equivalent in T-SQL to C#'s "throw;" to re-throw exceptions?

In C# one can do this:

try
{
    DoSomethingThatMightThrowAnException();
}
catch (Exception ex)
{
    // Do something with the exception
    throw; // Re-throw it as-is.
}

Is there something in T-SQL's BEGIN CATCH functionality that does the same?

Answer 1

You can use RAISERROR. From the MSDN documentation on RAISERROR:

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;

EDIT:

This is not really the same thing as c#'s throw or throw ex. As @henrikstaunpoulsen points out you don't get the original error number in the new error (RAISERROR is restricted in which numbers it can use). You would have to use some sort of convention and parse the information (if available) out of the message.

MSDN has an article Using TRY...CATCH in Transact-SQL and I used some of the code to create the test below:

use test;
GO

IF OBJECT_ID (N'usp_RethrowError',N'P') IS NOT NULL
    DROP PROCEDURE usp_RethrowError;
GO

CREATE PROCEDURE usp_RethrowError AS
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

    RAISERROR 
        (
        @ErrorMessage, 
        @ErrorSeverity, 
        @ErrorState,               
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
        );
GO

PRINT 'No Catch'
DROP TABLE XXXX

PRINT 'Single Catch'
BEGIN TRY
    DROP TABLE XXXX
END TRY
BEGIN CATCH
    EXEC usp_RethrowError;
END CATCH;

PRINT 'Double Catch'
BEGIN TRY
    BEGIN TRY
        DROP TABLE XXXX
    END TRY
    BEGIN CATCH
        EXEC usp_RethrowError;
    END CATCH;
END TRY
BEGIN CATCH
    EXEC usp_RethrowError;
END CATCH;

Which produces the following output:

No Catch
Msg 3701, Level 11, State 5, Line 3
Cannot drop the table 'XXXX', because it does not exist or you do not have permission.
Single Catch
Msg 50000, Level 11, State 5, Procedure usp_RethrowError, Line 25
Error 3701, Level 11, State 5, Procedure -, Line 7, Message: Cannot drop the table 'XXXX', because it does not exist or you do not have permission.
Double Catch
Msg 50000, Level 11, State 5, Procedure usp_RethrowError, Line 25
Error 50000, Level 11, State 5, Procedure usp_RethrowError, Line 25, Message: Error 3701, Level 11, State 5, Procedure -, Line 16, Message: Cannot drop the table 'XXXX', because it does not exist or you do not have permission.

Answer 2

In SQL 2012 they added the new THROW keyword, that can also be used to re-throw an exception

USE tempdb;
GO
CREATE TABLE dbo.TestRethrow
(    ID INT PRIMARY KEY
);
BEGIN TRY
    INSERT dbo.TestRethrow(ID) VALUES(1);
--  Force error 2627, Violation of PRIMARY KEY constraint to be raised.
    INSERT dbo.TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH

    PRINT 'In catch block.';
    THROW;
END CATCH;

http://msdn.microsoft.com/en-us/library/ee677615.aspx

Answer 3

Here is what I have used to rethrow an exception after rolling back the transaction. This gives the line number information of the error too.

BEGIN TRY
    BEGIN TRANSACTION    -- Start the transaction

    -- Do your work here

    -- Commit the transaction
    COMMIT TRANSACTION

END TRY

BEGIN CATCH
    -- There was an error, rollback the transaction
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

    -- Raise an error with the details of the exception
    DECLARE @ErrorMessage nvarchar(2048)
    DECLARE @ErrorProcedure nvarchar(128)
    DECLARE @ErrorState int
    DECLARE @ErrorLine int
    DECLARE @ErrorSeverity int

    SET @ErrorProcedure = ERROR_PROCEDURE()
    SET @ErrorLine = ERROR_LINE()
    SET @ErrorSeverity = ERROR_SEVERITY()
    SET @ErrorState = ERROR_STATE()
    SET @ErrorMessage = ''

    IF @ErrorProcedure IS NOT NULL
        SET @ErrorMessage = @ErrorMessage + @ErrorProcedure + ' ';

    IF @ErrorLine IS NOT NULL
        SET @ErrorMessage = @ErrorMessage + '[Line ' + CAST(@ErrorLine as nvarchar) + '] ';

    SET @ErrorMessage = @ErrorMessage + ERROR_MESSAGE()

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH

Answer 4

In order to prevent the repetition of procedure information/error/line numbers in multiple catch scenarios, I use a similar procedure, with the slight modification as follows:

IF @Error_Procedure <> OBJECT_NAME(@@PROCID)    
    BEGIN
        RAISERROR('[Procedure: %s]: Nest Level: %d; Line: %d; Error Number: %d; Message: %s',@Error_Severity,@Error_State,@Error_Procedure, @NestLevel, @Error_Line, @Error_Number, @Error_Message)
    END
ELSE
    BEGIN
        RAISERROR(@Error_Message,@Error_Severity,@Error_State)
    END

So if we have already caught and re-raised the error with this SP, we don't repeatedly add the additional information, so at the outer scope, we see only the error as originally re-raised.

In the examples posted above, the double-catch output would be the same as the single-catch output. I also include the nest level in the error message to aid with debugging.

Answer 5

you can raise exceptions using RAISEERROR

http://msdn.microsoft.com/en-us/library/ms178592.aspx

Answer 6

I generally use the following:

DECLARE @Outcome as bit
DECLARE @Error as int

BEGIN TRANSACTION 

-- *** YOUR TSQL TRY CODE HERE **** 


-- Capture the TSQL outcome. 
SET @Error = @@ERROR 

-- Set the Outcome to be returned to the .NET code to successful
SET @Outcome = 1


IF @Error <> 0
    BEGIN   
        -- An Error was generate so we invoke ROLLBACK
        ROLLBACK
        -- We set the Outcome to be returned to .Net to unsuccessful
        SET @Outcome = 0
    end

ELSE
    BEGIN
        -- The transaction was successful, invoke COMMIT
        COMMIT
    END



-- Returning a boolean value to the .NET code
Select @Outcome as Outcome