SQL Server Denali THROW statement to raise an exception

By: Arshad Ali | Read Comments | Print Arshad is a SQL and BI Developer focusing on Data Warehousing projects @ Microsoft India R&D Pvt Ltd. Related Tips: 1 | 2 | 3 | More

Problem

Prior to SQL Server 2005, writing code for exception handling was painful for T-SQL developers. SQL Server 2005 introduced structured exception handling with BEGIN TRY...BEGIN CATCH blocks. Though this inclusion made managing exceptions in T-SQL programming quite easier, it has some limitations. In this tip I am going to talk about some of these challenges and limitations, then show how a new command THROW in SQL Server Denali overcomes those items.

Solution

While writing T-SQL code we use the RAISERROR command to raise an exception. We normally use the RAISERROR command inside of a BEGIN TRY...END TRY block to raise an exception and handle it in the BEGIN CATCH...END CATCH block. Sometimes we need to raise the exception or re-raise the same exception from the BEGIN CATCH...END CATCH block to send it to an outer block or calling application and hence we use RAISERROR command inside the BEGIN CATCH...END CATCH block.

Generally speaking, there are two different ways we can use the RAISERROR command to raise an exception. First, we can pass the error message string as an argument to this command (in this case the error number will be 50000). The second, way is to pass the error number. When we use error number as a parameter to the RAISERROR command, the entry for that error number must exist in the sys.messages system table or the RAISERROR command itself will fail. Although this second approach sounds easy at first, it becomes a little difficult if you are calling procedures across instances (where you need to add error messages on all instances) or you are migrating you code from one instance to another and you forget to migrate the messages from the sys.messages system object. Let's see step by step how we can use RAISERROR command as well as new THROW command.

In Script #1, I am simply creating a table in the tempdb database for the our examples.

USE tempdb;
GO
CREATE TABLE dbo.Sample_Table
(
  column_1 int NOT NULL PRIMARY KEY,
  column_2 int NULL
);

In Script #2, my intent is to show how you can use the BEGIN TRY...END TRY and BEGIN CATCH...END CATCH blocks for structured exception handling. Inside the BEGIN TRY...END TRY block I am trying to insert duplicate records. In this circumstance, SQL Server throws an error (error number 2627). As a result of this error, control and execution will jump to the BEGIN CATCH...END CATCH block. Here we can gracefully handle the exception and continue with further processing or re-raise the exception.

Script #2 - Structured Exception Handling

BEGIN TRY TRUNCATE TABLE dbo.Sample_Table; INSERT dbo.Sample_Table VALUES(1, 1); PRINT 'After First Insert'; -- Msg 2627, Level 14, State 1 - Violation of PRIMARY KEY constraint INSERT dbo.Sample_Table VALUES(1, 1); PRINT 'After Second Insert'; END TRY BEGIN CATCH PRINT 'In Catch Block.'; END CATCH;

In Script #3, I have the same example as above, but this time I am re-raising the exception (to send it to the outer block or caller application). You can notice here the RAISERROR command itself fails. The reason is we are raising the exception by passing the system generated error number, which is not allowed to be used explicitly by the users (you can add your own user defined messages to the sys.messages table and then use the user defined message while raising an exception). Please note, when you raise an exception by passing the error number as an argument to RAISERROR command, that error number must exist in the sys.messages table (user defined messages can be added with the sp_addmessage system stored procedure).

BEGIN TRY
  TRUNCATE TABLE dbo.Sample_Table;
  INSERT dbo.Sample_Table VALUES(1, 1);
  PRINT 'After First Insert';
  -- Msg 2627, Level 14, State 1 - Violation of PRIMARY KEY constraint 
  INSERT dbo.Sample_Table VALUES(1, 1);
  PRINT 'After Second Insert';
END TRY
BEGIN CATCH
  DECLARE @ErrorNumber INT = ERROR_NUMBER();
  RAISERROR(@ErrorNumber, 16, 1) 
END CATCH;

In Script #4, I have the same example as above, but this time instead of passing the error number I am passing the error message (I have concatenated the error number and error message in one string) and it works fine. Please note, even though it works fine, the indicated line number is Line 12 of the RAISERROR command instead of Line 6 of the actual command which failed.

Script #4 - Re-raising exception with Error Message

BEGIN TRY TRUNCATE TABLE dbo.Sample_Table; INSERT dbo.Sample_Table VALUES(1, 1); PRINT 'After First Insert'; -- Msg 2627, Level 14, State 1 - Violation of PRIMARY KEY constraint INSERT dbo.Sample_Table VALUES(1, 1); PRINT 'After Second Insert'; END TRY BEGIN CATCH DECLARE @ErrorNumber INT = ERROR_NUMBER(); DECLARE @ErrorMessage NVARCHAR(1000) = ERROR_MESSAGE() RAISERROR('Error Number-%d : Error Message-%s', 16, 1, @ErrorNumber, @ErrorMessage) END CATCH;

In Script #5, I have the same example as above, but this time instead of using RAISERROR command to re-raise the exception I am using new THROW command. As you can see, this time it reports the exact error number (i.e. 2627) which caused the exception as well as the exact line number where the exception occurred.

Script #5 - Re-raising exception with the new THROW command

BEGIN TRY TRUNCATE TABLE dbo.Sample_Table; INSERT dbo.Sample_Table VALUES(1, 1); PRINT 'After First Insert'; -- Msg 2627, Level 14, State 1 - Violation of PRIMARY KEY constraint INSERT dbo.Sample_Table VALUES(1, 1); PRINT 'After Second Insert'; END TRY BEGIN CATCH PRINT 'Handle Exception here if required and re-throw'; THROW; END CATCH;

How does the THROW command differ from its counterpart the RAISERROR command?

Here are a few observations:

• First, its not mandatory to have a message available in sys.messages system object if you are using the error number or error ID.
• Second, by default the exception thrown using the THROW command has a severity level of 16 and you cannot change it.
• Third, you cannot use print style formatting with the THROW command although you can use the FORMATMESSAGE function to achieve the same results.
• Fourth, when you re-throw the exception with the THROW keyword the original error number and line number is preserved unlike the RAISERROR command where it is overwritten.

Additional Notes

• The MSDN documentation on RAISERROR states it has been deprecated and should not be used in further development, but when reviewing the system meta data (SELECT * FROM sys.dm_os_performance_counters WHERE [object_name] LIKE N'%:Deprecated Features%'), it appears that only the old style usage of the RAISERROR command has been deprecated.
• When using the THROW command, the last statement before the THROW must be terminated with a semicolon.
• The sample code, example and UI is based on SQL Server Denali CTP 1, so it might change in future CTPs or in the final/RTM release.

Next Steps

• As you begin to learn SQL Server Denali, consider upgrading your T-SQL skills to use the THROW command.
• If you are not properly handling error conditions, check out these tips - Error Handling Tips.
• Review the following additional resources:
  • RAISERROR related tip
  • RAISERROR command on MSDN
  • THROW command on MSDN
  • Review my all previous tips

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code