Microsoft® SQL Server® 2012 Bible

415

Chapter 16: Programming with T-SQL

16

Error Severity

Windows has established standard error-severity codes, listed in Table 16-2. The other

severity codes are reserved for Microsoft’s use. In any case, the severity code you’ll use for

your RAISERROR will almost always be 16.

TABLE 16 -2 Available Severity Codes

Severity Code Description

10 Status message: Does not raise an error but returns a message, such as a PRINT

statement.

11–13 No special meaning.

14 Informational message.

15 Warning message: Something may be wrong.

16 Critical error: The procedure failed.

Adding Variable Parameters to Messages

The error message can be a fi xed-string message or the error number of a stored message.

Either type can work with optional arguments.

The arguments are substituted for placeholders within the error message. Although several

types and options are possible, two useful placeholders are %s for a string and %i for a

signed integer. The following example uses one string argument:

RAISERROR ('Unable to update %s.', 14, 1, 'Customer');

Result:

Msg 50000, Level 14, State 1, Line 1

Unable to update Customer.

Stored Messages

The RAISERROR command can also pull a message from the sys.messages system view.

Message numbers 1–50,000 are reserved for Microsoft. Higher message numbers are available

for user-defi ned messages. The benefi t to use stored messages is that all messages are forced

to become consistent and numbered.

Note that with sys.messages stored messages, the message-number scheme is server-

wide. If two vendors, or two databases, use overlapping messages, then no division exists

between databases, and there’s no solution beyond recoding all the error handling on one of

the projects. The second issue is that when migrating a database to a new server, the mes-

sages must also be moved.

c16.indd 415c16.indd 415 7/30/2012 5:38:10 PM7/30/2012 5:38:10 PM

http://www.it-ebooks.info