Throw Statement in SQL Server 2012

In this article I will explain the use of the throw statement in SQL Server 2012. If you have programmed in languages like C# or other languages then you are probably familiar with the try, catch and throw statements. Transact-SQL also gives you this option to find an exception using a try/catch block. In SQL Server 2005/2008, RAISERROR has remained the only mechanism for generating your own errors. In SQL Server 2012, a new THROW statement is used to raise exceptions in your T-SQL code in place of RAISERROR. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

In SQL Server 2005/2008

In SQL Server 2005/2008, if you want to re-throw an error in a catch block of a TRY CATCH statement, you have to use RAISERROR with ERROR_MESSAGE() and ERROR_SEVERITY(). But in SQL Server 2012 you can use only a THROW statement to re-throw an error in a catch block of a TRY CATCH statement.

Example 1

In this example we use a select statement in a try block. If there is an error in a try block then it will throw the error to the catch block. The following is a sample T-SQL script with exception handling in SQL Server 2008:

BEGIN TRY

select from UserDetail -- select statement error

END TRY

BEGIN CATCH

DECLARE @ErrorMessage nvarchar(4000), @ErrorSeverity int

SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY()

RAISERROR (@ErrorMessage, @ErrorSeverity, 1 )

END CATCH

Now press F5 to execute it; the results will be:

Example 2

We divide a number by zero:

BEGIN TRY

DECLARE @VALUE INT

SET @VALUE = 12/ 0

END TRY

BEGIN CATCH

DECLARE @ErrorMessage nvarchar(4000), @ErrorSeverity int

SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY()

RAISERROR ( @ErrorMessage, @ErrorSeverity, 1 )

END CATCH

Now press F5 to execute it; the results will be: