Introduction

In order to improve error handling in TSQL scripts Microsoft has introduced throw statement in SQL Server 2012. This feature will help the database developers to handle errors more effectively.

Advantages of Using Throw Statement in SQL Server 2012

In sys.messages it is not required to have error_number parameter to exist.
Statement just before the THROW statement must be followed by (;) semicolon.
The severity level of the exception is always set to 16.

THROW SYNTAX AS MENTIONED IN MSDN

THROW [ { error_number | @local_variable },
{ message | @local_variable },
{ state | @local_variable } ]
[ ; ]

Sample TSQL Script to Demo THROW Statement

Execute the below sample TSQL script which has a Demo of THROW statement of SQL Server 2012.

DECLARE @ErrorNumber AS INT
,@ErrorMessage AS NVARCHAR(2048)
,@ErrorState AS TINYINT

BEGIN TRY

SELECT 10/0 AS ACertainError

END TRY

BEGIN CATCH

SELECT @ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorState = ERROR_STATE();
THROW 77777, @ErrorMessage, @ErrorState;

END CATCH
GO

Error Message

(0 row(s) affected)
Msg 77777, Level 16, State 1, Line 13
Divide by zero error encountered.

Error Handling Using Throw Statement in SQL Server 2012

In the above snippet you could see that the error message is re-thrown by the SQL Server.

Reference: - Throw Statement

Conclusion

It is recommended to use THROW statement in new development work within the TRY...CATCH statement.

Microsoft Technologies

Pages

Sunday, July 28, 2013

Improve Error Handling Using Throw Statement in SQL Server 2012