Microsoft® SQL Server® 2012 Bible

421

Chapter 16: Programming with T-SQL

16

The following sample demonstrates a CATCH block using the CATCH functions and a

RAISERROR to report the error to the client. The contents of the error functions are passed

to variables so a custom error string can be assembled for the RAISERROR:

BEGIN CATCH

DECLARE

@Error_Severity INT,

@Error_State INT,

@Error_Number INT,

@Error_Line INT,

@Error_Message VARCHAR(245);

SELECT

@Error_Severity = ERROR_SEVERITY(),

@Error_State = ERROR_STATE(),

@Error_Number = ERROR_NUMBER(),

@Error_Line = ERROR_LINE(),

@Error_Message = ERROR_MESSAGE();

RAISERROR ('Msg %d, Line %d: %s',

@Error_Severity,

@Error_State,

@Error_Number,

@Error_Line,

@Error_Message);

SELECT @Error_Number;

END CATCH;

Throw

The information captured by a THROW is quite similar to RAISERROR and can largely

be confi gured, with the exception of severity level, which will always be 16. In its sim-

plest form, THROW looks like the following example, where 50001 represents the error

number, 'Thrown Error Message' represents the error message, and 1 represents the

error state.

THROW 50001, 'Thrown Error Message', 1;

If custom error information is not needed, the keyword THROW can be included in the

CATCH block without any provided parameters.

BEGIN TRY

SELECT 1/0;

c16.indd 421c16.indd 421 7/30/2012 5:38:12 PM7/30/2012 5:38:12 PM

http://www.it-ebooks.info