SQL Theory: Throw in SQL Server 2012

Throw in SQL Server 2012

By Jignesh Trivedi on Oct 08, 2013

Throw keyword is introduced with SQL server 2012. Throw is used to raises exception and transfers execution to a CATCH block in SQL server.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 TRYBEGINCATCH 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 BEGINPRINT '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 BEGINPRINT '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.

T-SQL Enhancement: THROW

14-06-2011

When bulding solid T-SQL solutions, it is often necessary to implement some level of error handling. One of the most used features provided by SQL Server, is RAISERROR. This has been around since SQL Server 7.0, but with SQL Server 2005 TRY/CATCH was born. This significally improved error handling which now resembles error handling as we know it from modern development languages like C#.

Now error handling has been improved even more, with the introduction of THROW in Denali.

One of the differences between TRHOW and RAISERROR is that THROW always raises an error with severity 16. This cannot be changed. An exampel of usage:

1. THROW 50000, 'This is a custom error message', 1    

This gives the following error message as expected:

1. Msg 50000, Level 16, State 1, Line 1  
2. This is a custom error message  

So, why is this any different than RAISERROR?

One of the problems with RAISERROR is that it does not honor the XACT_ABORT option if set to ON.

Example:

1. SET XACT_ABORT ON  
2. BEGIN TRAN  
3. INSERT INTO MyTable (col1) VALUES (1);  
4. THROW 50000, 'This is my custom error being raised', 1  
5. INSERT INTO MyTable (col1) VALUES (2)  
6. COMMIT  
7. SET XACT_ABORT OFF  

The output of this query is:

1. (1 row(s) affected)  
2. Msg 50000, Level 16, State 1, Line 4  
3. This is my custom error being raised  

In this example we throw an error in the middle of a transaction, with XACT_ABORT ON. The output shows that the first insert is performed with 1 row affected. Then the error is raised, and the second insert is never executed. The entire transaction is automatically rolled back, leaving no inserted rows in the table.

If we however used RAISERROR instead of THROW, the results would have been different:

1. SET XACT_ABORT ON  
2. BEGIN TRAN  
3. INSERT INTO TransactionTest (val) VALUES (1);  
4. RAISERROR('This is my custom error being raised', 16, 1)  
5. INSERT INTO TransactionTest (val) VALUES (2)  
6. COMMIT  
7. SET XACT_ABORT OFF  

Results:

(1 row(s) affected)  
Msg 50000, Level 16, State 1, Line 4  
This is my custom error being raised  
(1 row(s) affected)  

The exact same error is still raised, but now the second insert is also performed, and the automatic rollback is not performed.

This is one of the minor improvements of THROW. Another big change is the possibility to re-throw an error back the the upper level. Imagine you have some code within a TRY block, where a number of different errors might occur. You might want to handle “divide by zero” errors directly in the t-sql code, but let the upper level handle all other errors. With RAISERROR this cannot be achieved, because you can only raise user defined errors, with error number higher than 50000. So if you encounter ERROR_NUMBER() = 8114 in  your CATCH block, your only option is to raise a new user defined  error having the same meaning. Etc. error number 51000 = “Error converting data type varchar to numeric.” This is no way elegant.

This can easily be solved using THROW:

1. DECLARE @str VARCHAR(10) = '3'  
2. DECLARE @i INT  
3. SET NOCOUNT ON  
4. BEGIN TRY  
5. SET @i = CAST(@str AS INT)  
6. SET @i = 10/@i  
7. PRINT @str + ' was successfully converted to an INT'  
8. PRINT 'Division successfull'  
9. END TRY  
10. BEGIN CATCH  
11. IF ERROR_NUMBER() = 8134  
12. BEGIN  
13. PRINT 'Divide by zero error occurred. Logging error.'  
14. END  
15. ELSE  
16. BEGIN  
17. PRINT 'Other error occorred with @str = ''' + @str + '''. Re-throw the error to upper level for handling...';  
18. THROW;  
19. END  
20. END CATCH;  

The input string @str is first converted to an integer, and then the number is used in a division. This requres that the value of @str is an integer, and it should be different than 0.

The conversion and division is located in the TRY block, and depending on the input string, either an 8134 or 245 error is raised – or no error at all. Within the CATCH block we check for error 8134, making it possible to log that specific error. If the error is different than 8134, then we simply invoke “THROW;”.

THROW without any arguments is only allowed within a CATCH block, and simply re-throws the original error, letting the upper level handle the error.

When executing the code with @str = ‘3’ no error occurs, and the message output is:

1. 3 was successfully converted to an INT  
2. Division successfull  

Executiong with @str = ‘0’ would give a “divide by zero” error, which should be handled. The output is:

Divide by zero error occurred. Logging error.

With @str = ‘aNumber’ the following error is re-thrown:

Other error occorred with @str = 'aNumber'. Re-throw the error to upper level for handling...

Msg 245, Level 16, State 1, Line 6  
Conversion failed when converting the varchar value 'aNumber' to data type int.  

The introduction of THROW simply takes error handling to the next level.