THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Roman Rehak

How to rethrow errors in T-SQL

If you do application development and you are used to rethrowing errors in a catch block, you may have noticed that error handling in T-SQL still doesn't support this functionality. However, you can easily emulate this functionality by rolling out your own "rethrow" stored procedure and using RAISERROR to throw the error back to the client. BOL contains a good example of that, there is a stored procedure called usp_RethrowError. It uses the built-in error functions and RAISERROR to create a new error with the same message text, number and other details.

 Here is the code:

CREATE PROCEDURE [dbo].[usp_RethrowError]
AS -- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
  RETURN ;

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

    -- Assign variables to error-handling functions that
    -- capture information for RAISERROR.
SELECT  @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ;

    -- Building the message string that will contain original
    -- error information.
SELECT  @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
        'Message: ' + ERROR_MESSAGE() ;

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @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
 

Sample code showing how to use it:

DECLARE @Zero INT
SET @Zero = 0

BEGIN TRY
 SELECT 5 / @Zero
END TRY
BEGIN CATCH
 PRINT 'We have an error...'
 EXEC usp_RethrowError
END CATCH

Published Saturday, December 01, 2007 9:40 PM by roman

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Tim Hill said:

There is of course a bug in the usp_RethrowError, if is not always possible to rethrow an error with the same severity as was caught. Only a member of the sysadmin role can raise an error with a severity greater than or equal to 19, however anyone can catch it. The options are either to lower the severity when you throw it, or make usp_RethrowError execute in a sysadmin role, neither are ideal.

December 2, 2007 1:18 PM
 

Alexander Kuznetsov said:

unfortunately, if you are using ERROR_NUMBER() to handle a specific error, such as a deadlock, this is not gonna work. I don't try to emulate throw, I usually go for a language which natively let me rethrow, such as C#, and do all the error handling using a modern language.

December 3, 2007 6:22 PM
 

Aviv Zucker said:

There is a drawback in using such method - it will always throw Msg number 50000. If you count on the number in your exception handling, this is a problem.

Aviv.

December 4, 2007 4:13 AM
 

Hugo said:

I agree with Aviv Zucker, this is a problem... Can anyone solve it?

June 27, 2008 12:11 PM
 

Carlos V said:

Hugo: You could define your own custom error numbers in sys.messages (using sp_addmessage) and re-throwing the exception like this:

RAISERROR( ErrorNumber, severity, state)

instead of

RAISERROR( Message, severity, state)

Not a pretty solution but it's the only workaround I can think of.

October 14, 2008 10:07 AM
 

Jim said:

I think there's also another bug.  RAISERROR only generates errors with state from 1 through 127. Because the Database Engine may raise errors with state 0, it should check the error state returned by ERROR_STATE before passing it as a value to the state parameter of RAISERROR.

December 2, 2008 1:09 PM
 

Jacob Sebastian said:

There is a connect item that requests for a provision to rethrow an error after catching it. The status of the connect item is set to 'resolved'.   Anyone knows if this is added to SQL Server 2008?

Here is the connect item: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127228&wa=wsignin1.0

January 20, 2009 4:34 AM
 

Peleg said:

The Problem is, that when you make a RAISERROR (after you did  try/catch in a STROED PROCEDURE), it seem's it dosen't have the same effect.

beacuse when i wrapped the code in the SP with try/catch, and then used RAISERROR with my own msg_id and Sevirity 16, i stopped getting the error in my application (in this case vb6).

it seems that the RAISERROR dosen't have the same effect like in an exception the SQL SERVER 2005/2008 throws.

as i see from here the usp_RethrowError is just a wrap for RAISERROR.

July 31, 2009 9:30 AM
 

anoopsihag said:

It will always add the rethrow error detail such as procedure name ,line etc

January 31, 2011 6:52 PM
 

Jeff Moden said:

Alexander Kuznetsov said:

"unfortunately, if you are using ERROR_NUMBER() to handle a specific error, such as a deadlock, this is not gonna work. I don't try to emulate throw, I usually go for a language which natively let me rethrow, such as C#, and do all the error handling using a modern language."

Agreed... but what if you're working on a purely T-SQL solution?  What do you do then?

October 19, 2012 5:49 PM
 

bsargent said:

Thanks Bud! All the previous comments are quick to point out the issues without providing possible solutions. For me this will do fine; yes, it may not be perfect, but nothing is.

February 20, 2013 4:25 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement