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:

Now using a THROW statement in place of a RAISERROR:

BEGIN TRY 

select * from UserDetail

END TRY  

BEGIN CATCH 

throw

END CATCH 

Now press F5 to execute it.

In SQL Server 2012

In SQL Server 2012, THROW can appear only inside a CATCH block. In SQL Server 2012 you can only use a THROW statement in a catch block when an unexpected error occurs in a TRY block. A new THROW statement is used to raise exceptions in your T-SQL code in place of RAISERROR. In SQL Server 2012, by using the Throw keyword, the preceding script will be changed to this:

Example 1 ( Using THROW Statement)

BEGIN TRY   

select  from UserDetail   -- select statement error

 END TRY  

 BEGIN CATCH 

throw

 END CATCH 

 

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

 

 

Example 2 ( Using THROW Statement)

 

We divide a number by zero. A THROW statement is used to raise exceptions; see:

 

BEGIN TRY 

   DECLARE @VALUE INT 

   SET @VALUE = 12/ 0 

 END TRY  

 BEGIN CATCH 

  throw 

 END CATCH 

 

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