Microsoft® SQL Server® 2012 Bible

423

Chapter 16: Programming with T-SQL

16

TABLE 16 - 4 Differences between RAISERROR and THROW

RAISERROR THROW

A msg_id passed to RAISERROR must be con-

tained in sys.messages.

Error_number parameter does not need to

exist in sys.messages.

Msg_str parameter may contain printf for-

matting styles.

Message parameter does not accept printf

styles.

Severity parameter specifi es error severity. No severity parameter; always set to 16.

Nested Try/Catch and Rethrown Errors

Any error can bubble up through every layer of stored procedures until it’s caught by a try/

catch block or it reaches the client. Visualizing the call stack — the stack of procedures

that have executed or called other stored procedures — it’s possible for lower level, or

nested, stored procedures to use this principle to send, or rethrow, errors to higher-level

stored procedures in the call stack.

TRY/CATCH blocks can easily be nested even if the nesting is unintentional. If one stored

procedure calls another stored procedure and both procedures are well written, with

TRY/CATCH blocks, then not only are the stored procedures nested, but the TRY/CATCH

blocks are nested, too.

In the following example, the TopProc executes, or calls, the CalledProc. A divide by

zero error in CalledProc causes the code to jump to the CATCH block. The CATCH block

issues a THROW.

The TopProc receives the error that was raised by the CalledProc. It sees the error as

any other type of error and therefore jumps down to its CATCH block. The THROW in the

TopProc is executed, and it too raises an error. This time the raised error is seen by the

client, in this case Management Studio:

CREATE PROC TopProc

AS

BEGIN TRY

EXEC CalledProc

END TRY

BEGIN CATCH

THROW 50001, 'TopProc Raiserror',1

END CATCH

GO

CREATE PROC CalledProc

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

http://www.it-ebooks.info