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.