THROW in
Error Handling
Error
handling is now easier with the introduction of the THROW command in SQL Server
2011.
Legacy method (SQL 2005
onwards)
In
previous versions, RAISERROR was used to show an error message. RAISERROR
requires a proper message number to be shown when raising any error. The
message number should exist in sys.messages. RAISERROR cannot be used to
re-throw an exception raised in a TRY..CATCH block.
2011 method
Unlike
RAISERROR, THROW does not require that an error number to exist in sys.messages
(although it has to be between 50000 and 2147483647). All exceptions being
raised by THROW have a severity of 16.
You
can throw an error using Throw as below:
THROW
50001, ‘Error message’, 1;
This
will return an error message:
Msg 50001, Level
16, State 1, Line 1 Error message
THROW
even allows for re-throwing an exception caught in a TRY..CATCH block, which
RAISERROR was not able to handle:
BEGIN TRY
SELECT ‘Using Throw’
SELECT 1 / 0
END TRY
BEGIN CATCH
–Throw error
THROW
END CATCH
The
above code snippet produces this output:
|
(1 row(s) (0 row(s) Msg 8134, Level Divide by zero |
THROW
has now made the developer’s life much easier, and developers can now code
independent of the Tester’s input on the exception message.
NOTE:
The current version of 2011 Books Online indicates that RAISERROR has been
deprecated. Due to this, the use of RAISERROR should be minimized.