Microsoft® SQL Server® 2012 Bible

420

Part IV: Programming with T-SQL

1. If the batch uses logical transactions (BEGIN TRAN/COMMIT TRAN), then, depend-
   ing on the error and situation, the error handler might need to roll back the trans-
   action. If this is the case, rolling back the transaction is recommended as the fi rst
   action so that any locks the transaction might be holding are released.


2. If the error is one that the stored procedure logic detects, and it’s not a SQL Server
   error, then raise the error message so that the user or front-end application is
   informed.


3. Optionally, log the error to an error table.


4. Terminate the batch. If it’s a stored procedure, user-defi ned function, or trigger,
   then terminate it with a RETURN command.

When an error occurs in the TRY block and execution is passed to the CATCH block,

the error information is also passed to the CATCH block. At this point, you have two

options. You may use the RAISERROR functionality that has been available in SQL

Server for quite some time, or you may use the new THROW command introduced in SQL

Server 2012. First, walk through the traditional RAISERROR functionality, and then

look at THROW.

RAISERROR

RAISERROR enables you to examine the details of the error using the error functions listed

in Table 16-3. These functions are designed specifi cally for the CATCH block. Outside a

CATCH block, they always return a null value.

TABLE 16 -3 Catch Functions

Error Function Returns

Error_Message() The text of the error message

Error_Number() The number of the error

Error_Procedure() The name of the stored procedure or trigger in which the error

occurred

Error_Severity() The severity of the error

Error_State() The state of the error

Error_Line() The line number within the batch or stored procedure that generated

the error

Xact_State() Whether the transaction can be committed (see Chapter 47)

These CATCH functions retain the error information of the error that fi red the CATCH

block. They may be called multiple times and still retain the error information.

c16.indd 420c16.indd 420 7/30/2012 5:38:11 PM7/30/2012 5:38:11 PM

http://www.it-ebooks.info