Throw in SQL Server 2012

Introduction

The THROW statement is introduced with SQL Server 2012. Throw is used to raise an exception and transfers execution to a CATCH block in SQL Server.
 
SQL Server 2005 introduced the TRY ... CATCH block to effectively handle errors within T-SQL. Using a CATCH block we can easily write the error handling T-SQL needs to execute whenever a problem occurs within a TRY block.
 
The RAISERROR function can help us raise the error. The RAISERROR requires the user to define a message to be added into the SYS.MESSAGES table before we use it to raise the error. The THROW statement does not require an error number to exist within the sys.messages table but the error number must be greater than 50000. All exceptions raised by the THROW statement will have a severity of 16.
 
Syntax

THROW error number, message, State
 
Arguments/Parameters

The following are the Arguments/Parameters:

  • Error number: error number may be a constant or variable and it represents the exception. It is an INT and must be greater than or equal to 50000 and less than or equal to the maximum value of INT (in other words 2147483647).
  • Message: this is a string value that represents the description of the exception. The data type of the message argument is NVARCHAR (2048).
  • State: it is a constant or variable value that must be between 0 and 255, that represents the state to associate with the message. The data type of sate is TINYINT.

Point to consider when using the "THROW" expression:

  • The statement before the THROW statement must be followed by the statement terminator, in other words semicolon (;).
  • If the THROW statement is used without any argument, it must be inside a CATCH block.
  • If a TRY ... CATCH block is not available then the session is ended.

Example

-- Simple Throw example

THROW 52000, 'New exception is thrown.', 1;

-- output

Msg 52000, Level 16, State 1, Line 1

New exception is thrown.

 

--THROW example with TRY … CATCH block

 

BEGIN TRY

-- write your SQL statements.

DECLARE @a INT ='test'

END TRY

BEGIN CATCH

THROW;

END CATCH

 

-- output

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value 'test' to data type int.
 
Differences between RAISERROR and THROW

THROW RAISE ERROR
THROW is introduced with SQL Server 2012. It is very simple and easy to use. RAISE ERROR was introduced with SQL Server 2005.
We can re-throw the original exception that was caught with in the TRY...CATCH block. To do this just specify the THROW without a parameter.

Example
 

BEGINTRY

 DECLARE @result INT

--Generate casting error

 SET @result = 'This is test'

ENDTRY

BEGINCATCH

 THROW

ENDCATCH

 

Result

 

Msg 245,Level 16,State 1, Line 16

Conversion failed when converting the varchar value'This is test'to data type int.

We cannot re-throw the original exception that is invoked the CATCH block. It always raises a new exception and the result, original exception is lost.

Example

BEGIN TRY
DECLARE
@result INT
--Generate casting error
  SET @result= 'This is test'
END TRY
BEGINCATCH
 DECLARE @ErrorMessageNVARCHAR(2048),
         @ErrorSeverity INT,
         @ErrorState INT
 SELECT
   @ErrorMessage =ERROR_MESSAGE(),
   @ErrorSeverity =ERROR_SEVERITY(),
   @ErrorState =ERROR_STATE()
 
 RAISERROR (@ErrorMessage, @ErrorSeverity,@ErrorState)
END CATCH
 

Result

Msg 50000, Level 16, State 1, Line 16
Conversion failed when converting the varchar value 'This is test' to data type int.

There is no severity parameter. The exception severity is always set to 16 until re-throwing from a CATCH block The Severity parameter specified the severity of the exception.
It requires a semicolon (;) as a statement terminator. The statement before the THROW must have a semicolon. It does not require any statement terminator.

With the use of THROW, we cannot raise the system exception. But when throw is used within a CATCH block it can re-throw the original exception.

Example

THROW 15062, 'The guest user cannot be mapped to a login name.', 0

 Result

Msg 35100, Level 16, State 10, Line 1

Error number 15062 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647

 

RAISEERROR is able to generate a system exception

Example

RAISERROR (15062, 16,0)

Result

Msg 15062, Level 16, State 0, Line 1

The guest user cannot be mapped to a login name.

 
We can raise a user defined error message with a new message id without defining it in the sys.messages table. RAISEERROR accepts a message id or string but when we use a message id it must be defined in the sys.Messages table.
The message string parameter does not accept the printf formatting style. The message string parameter can contain a printf formatting style.

The statement after a THROW statement will not be executed as a batch process.

Example

BEGIN
PRINT 'Before THROW';
 THROW 5000,'This is THROW Test',1
 PRINT 'After THROW'
END

Result

Before THROW
Msg 50000, Level 16, State 1, Line 3
This is THROW Test

The statement after a RAISEERROR statement will be executed as a batch process.

Example

BEGIN
PRINT 'Before RAISERROR'
 RAISERROR('This is RAISERROR Test',16,1)
 PRINT'After RAISERROR'
END

Result

Before RAISERROR
Msg 50000, Level 16, State 1, Line 3
This is RAISERROR Test
After RAISERROR

The THROW statement always requires an additional message for error code 50000 and above. When RAISERROR is used without an error code, SQL Server assigns error code 50000.

Summary

RAISERROR and THROW are both used to generate a custom error. Both have their own advantages and disadvantages. The differences between these two are explained above. If we are a SQL Server 2012 user then we can use THROW instead of RAISERROR to raise our own errors. RAISERROR is still supported by SQL Server, so that with the help of this function we can raise an error with less severity.

Up Next
    Ebook Download
    View all
    Learn
    View all