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

you can raise exceptions using RAISEERROR

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

Answer 3

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

Answer 4

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 5

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.