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:
