SQL Server – Error handling using Throw

MS-SQL Server 2012 introduced a new way of error handling using THROW statement. Prior to SQL Server 2012 we had to use RAISEERROR for error handling.

Throw statement raises an exception and transfers the execution to a CATCH block of a TRY…CATCH construct. Session will be ended if Throw is used without TRY…CATCH construct.

Syntax:

THROW [ { error_number | @local_variable },

{ message | @local_variable },

{ state | @local_variable }

] [ ; ]

To use error number in throw statement, it is not required to add the message in sys.messages table but the error number should be greater than equal to 50000. It can only raise severity of 16. Throw can be used without parameter in side CATCH construct.

You can find the difference between THROW and RAISERROR here.

The interesting point about throw is that you can re-throw the original error information from the catch block without passing any parameter.

Example:

Begin Try
    print 'Start'
    Begin Try
        Declare @Name varchar(20) = '$XYZ'
        If (LEFT(@Name,1)='$')
        Begin
            Throw 51234,'Name should not starts with $',1
        End
    End Try
    Begin Catch
        Throw;
    End Catch
End Try
Begin Catch
   Select ERROR_MESSAGE() As Error
End Catch

Output:

Reference: http://msdn.microsoft.com/en-us/library/ee677615

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

August 11, 2012 - Posted by SQLcommitted | SQL SERVER 2012, Transact-SQL | Error Handling, Exception Handling, SQLcommitted, T-SQL, Throw, Transact-SQL