THROW in SQL SERVER

Before SQL SERVER 2012 (Denali), for exception handling, we had below options:-

·         Try… Catch block to catch the exceptions raised by application.

·         RAISERROR () to throw back the error to application.

But what if........    

·         We want to re-raise the same error then how to maintain the Error Number?

·         We want to send the error by some another way then how to maintain the original error line number?

SQL Server 2012 came up with the solution of above mentioned limitations. SQL Server 2012 introduced “THROW” command to overcome with the said limitation. THROW allows us to re-throw an exception caught in an exception handling block.

Advantages:-      

·         No compulsion, to define the error number in sys.messages, which code is going to THROW.

·         No severity parameter. THROW always take severity as 16.

·         We can re-raise the error with the help of THROW command.

·         While re-raising the error, we can maintain the original error line number, with the help of THROW.

·         While re-raising the error, we can maintain the original error number, with the help of THROW.

·         When we use THROW in TRY block then it will work as RAISERROR().

Limitations: -

·         Statement, before the THROW command, must have semicolon (;) statement terminated.

·         We cannot use print style formatting with the THROW command although you can use the FORMATMESSAGE function to achieve the same results.

·         When using THROW you have to define an error number as well as a message (and state), unless you re-throw an exception.

Syntax:-           THROW [ { error_number | @local_variable }                , { message | @local_variable }               ,  { state | @local_variable }] [ ; ]

·         error_number

o   Is a constant or variable that represents the exception. error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.

·         message  

o   Is a string or variable that describes the exception. Message is nvarchar(2048).

·         state

o   Is a constant or variable between 0 and 255 that indicates the state to associate with the message. State is tinyint.

EXAMPLES:-

1)    Without TRY...CATCH block :-

2)    Within TRY block:-

3)    Within CATCH block, without user defined value:-

4)    Within CATCH block, having user defined parameter



5)    To re-raise the exception with Error Number :-

a)    First have a look on RAISERROR example :- (please note below, error number got changed)

b)    Now look on THROW example :- (please note below, error number didn’t get changed)

6)    To re-raise the exception having original error line number:-

a)    First have a look on RAISERROR example :- (please note below, line number got changed)

b)    Now look on THROW example :- (please note below, line number didn’t get changed)