How to use THROW command (Error Handling) in SQL Server ?

Leave a comment (4) Go to comments

What is THROW in SQL Server ?

OR

What are the enhancements done for Error Handling in SQL Server Denali (2011)?

THROW is an TSQL enhancement, for ERROR/exception HANDLING, done in SQL Server Denali (2011).  In previous releases SQL Server 2005,2008 and 2008 R2, we uses TRY/CATCH for Exception Handling in TSQL statements.

What is the limitation with existing, TRY/CATCH (exception handling technique) ?

If we use TRY/CATCH block we were not able to get the actual SQL Server Error Message as CATCH was masking original error metadata: error number/severity/state, error text, origin line etc. This is very simple, but we can not get in SQL Server 2005/2008 and R2. For example in the following picture, we are missing the actual error number/severity/state, error text

How to use THROW command (Error Handling) in SQL Server ? What is THROW in SQL Server what is FORMATMESSAGE sql server try catch limitation sql server FORMATMESSAGE() sql server denali exception handling sql server 2011 exception handling sql server 2011 enhancement Incorrect syntax near THROW How to use THROW SQL Server How to use THROW in EXCEPTION HANDLING

But if we use the same code without TRY/CATCH, we encountered the error, which specifies Error details like number,severity,state,line

How to use THROW command (Error Handling) in SQL Server ? What is THROW in SQL Server what is FORMATMESSAGE sql server try catch limitation sql server FORMATMESSAGE() sql server denali exception handling sql server 2011 exception handling sql server 2011 enhancement Incorrect syntax near THROW How to use THROW SQL Server How to use THROW in EXCEPTION HANDLING


Now Some one might say, we can use RAISERROR to accomplish this objective. But for using  RAISERROR , a user defined error message mush be added to sys.messages table prior to use. otherwise, you will get an message like

How to use THROW command (Error Handling) in SQL Server ? What is THROW in SQL Server what is FORMATMESSAGE sql server try catch limitation sql server FORMATMESSAGE() sql server denali exception handling sql server 2011 exception handling sql server 2011 enhancement Incorrect syntax near THROW How to use THROW SQL Server How to use THROW in EXCEPTION HANDLING

This Limitation was removed in SQL Server Denali (2011) using THROW. Lets take a quick example of this

How to use THROW in EXCEPTION HANDLING ?

THROW can be use independently or with in TYR/CATCH block. So THROW will remove limitations identified for TRY/CATCH and RAISEERROR by re-throw the same error to the calling application/procedure. Sample code,

BEGIN TRY
    SELECT 1/0
END TRY
BEGIN CATCH
    PRINT 'Error Message : ' + ERROR_MESSAGE();
    THROW
END CATCH

How to use THROW command (Error Handling) in SQL Server ? What is THROW in SQL Server what is FORMATMESSAGE sql server try catch limitation sql server FORMATMESSAGE() sql server denali exception handling sql server 2011 exception handling sql server 2011 enhancement Incorrect syntax near THROW How to use THROW SQL Server How to use THROW in EXCEPTION HANDLING

Note :

  1. It’s must to end the TSQL Statement by ; (semi colon) before using THROW.
  2. Some functionality on RAISERROR is now in future deprecation list. (Check Comment posted by TechVsLife)
  3. Microsoft BOL [More Information]
  4. FORMATMESSAGE() ensures that there is no duplicity of error messages in sys.messages, Cancelling this text based on Comment, will spend more time on this.

If you liked this post then,

Subscribe to this Blog via Email:

Click here to Subscribe to FREE email updates from “DBATAG “, so that you do not miss out anything on SQL Server !!!

TAGS : What is THROW in SQL Server,sql server try catch limitation,sql server 2011 enhancement,How to use THROW in EXCEPTION HANDLING,How to use THROW SQL Server,Incorrect syntax near ‘THROW’,sql server 2011 exception handling , sql server Denali exception handling

EOF - How to use THROW command (Error Handling) in SQL Server ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

4 Comments.

  1. Hi Aaron,

    Thank you very much for reviewing this article.

    I think it was too early for me to write a post on this. Let me do some more research on this.

    I just corrected typo error for “Semi-colon is a statement terminator”

    Love to see your comments on the post.

  2. At the beginning, you say:

    “For example in the following picture, we are missing the actual error number/severity/state, error text”

    That is not a problem with CATCH. That is a problem with the way you’re using it. You are only looking at ERROR_MESSAGE() but there are several other system functions you can use to get what you’re after:

    ERROR_NUMBER()
    ERROR_SEVERITY()
    ERROR_STATE()
    ERROR_LINE()
    ERROR_MESSAGE()
    ERROR_PROCEDURE()

    I’m not saying this means THROW isn’t better for certain things, but this isn’t one of them. You should review the TRY/CATCH topic:

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

    Also, you say that a semi-colon will start a new batch; this is not true. Semi-colon is a statement terminator, not a batch terminator.

    Also I am not sure how FORMATMESSAGE() “ensures that there is no duplicity of error messages in sys.messages”? Could you elaborate? All FORMATMESSAGE() does is allow you to substitute parameters into a message stored in sys.messages. It doesn’t really “ensure” anything and I’m not sure what potential duplicity you’re talking about.

  3. Thanks TECHVsLife for pointing this out. Let me correct this.

  4. >>”RAISERROR is now in future deprecation list and should abandon its use.”
    That is NOT correct. RAISERROR is NOT deprecated and is not planned to be deprecated. Only the **oldstyle** RAISERROR with integer syntax is deprecated, not the most commonly used, current RAISERROR syntax: See BOL:
    The RAISERROR (Format: RAISERROR integer string) syntax is deprecated. Recommendation: Rewrite the statement using the current RAISERROR syntax.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.