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