SQL Server 2012 Throw Statement Introduction

By: Ashish Kumar Mehta   |   Read Comments   |   Related Tips: More > Error Handling

Problem

SQL Server 2012, has introduced the THROW statement which can be used for enhanced error handling. In this tip we will discuss how the THROW statement in SQL Server 2012 can help developers to effectively handle errors.

Solution

Microsoft introduced the TRY…CATCH construct in SQL Server 2005 which helped database developers to effectively handle errors within their T-SQL code. Using the CATCH block a developer could easily write the error handling logic that needs to be executed whenever a problem occurs within the TRY block. Prior to the introduction of TRY…CATCH construct developers had to use the @@ERROR in-built system function to check for error conditions after every operation. In the previous versions of SQL Server you had to use RAISERROR statement to show an error message. However, a RAISERROR requires a user defined message to be added to sys.messages table before you use it to raise an error.

In SQL Server 2012, when you use THROW statement it doesn’t require an error number to exist within the sys.messages table however, the error number used should be greater than 50000. It is important to note that all exceptions raised using the THROW statement will have a severity of 16 and the statement before the THROW statement must be followed by the semicolon (;) statement terminator.

Using THROW Statement in SQL Server 2012

Let us go through a simple example to demonstrate the THROW statement in SQL Server 2012.

SYNTAX: - 
THROW [ { error_number | @local_variable },
{ message | @local_variable },
{ state | @local_variable }
] [ ; ]
Use TempDB
GO
CREATE TABLE ErrorHistory
(
 ErrorTime   DATETIME
,ErrorSeverityLevel  NVARCHAR(100)
,ErrorMessage   NVARCHAR(1000)
)
GO
BEGIN TRY
 DECLARE @TryDivision int = 10/0
END TRY
BEGIN CATCH
 /* Insert Error Information & Then Re-Throw the error message received */
 INSERT INTO ErrorHistory VALUES(GETDATE(), ERROR_SEVERITY(), ERROR_MESSAGE());
 THROW;
END CATCH
GO
SELECT * FROM ErrorHistory
GO

In the above snippet you could see that in the code CATCH block the error information is instered into a table and the error message is re-thrown to the client application.

Next Steps

• Read THROW (Transact-SQL)
• Stay tuned for more tips on SQL Server 2012 and check out these related tips:
  • New Date and Time Functions in SQL Server 2012
  • New Logical Functions in SQL Server 2012 (IIF and CHOOSE)
  • New Data Type Conversion Functions in SQL Server 2012
  • Creating a SQL Server 2012 User Defined Server Level Role
  • SQL Server 2012 Contained Database Feature

About the author

Ashish has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips

We Recommend

• More Database Developer Tips...

Print

Tweet

Become a paid author

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 Signup for our newsletter

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