Tech Math 2 Lecture Notes

By George Cox,2014-07-01 16:18
5 views 0
Tech Math 2 Lecture Notes

SQL Server 2000 Notes Chapter 12, Volume C Page 1 of 3

    Chapter 12: Stored Procedures, Volume C: Error Handling

    SQL Server does not have an error handling mechanism. So, you have to catch all errors yourself and do something with them…

The @@Error Function:

    ; When Microsoft? SQL Server? completes the execution of a Transact-SQL statement, @@ERROR is

    set to 0 if the statement executed successfully.

    ; If an error occurs, an error message is returned. @@ERROR returns the number of the error message

    until another Transact-SQL statement is executed.

    ; You can view the text associated with an @@ERROR error number in the sysmessages system table (in

    the master database).



    %ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict occurred in database '%.*ls', table


    ; Because @@ERROR is cleared and reset on each statement executed, check it immediately following

    the statement validated, or save it to a local variable that can be checked later.

    ; One use: check for an error using @@ERROR, and then print a more helpful message…

    Example: write a sproc that gives a kinder, gentler error message for updates to the Customers table

The RAISERROR Statement:

    ; Returns a user-defined error message and sets a system flag to record that an error has occurred.

    ; Using RAISERROR, the client can either retrieve an entry from the sysmessages table or build a

    message dynamically with user-specified severity and state information.

    ; After the message is defined it is sent back to the client as a server error message.


    RAISERROR ( { msg_id | msg_str } { , severity , state }

     [ , argument [ ,...n ] ] )

     [ WITH option [ ,...n ] ]



    Is a user-defined error message stored in the sysmessages table. Error numbers for user-defined error messages

    should be greater than 50,000. Ad hoc messages raise an error of 50,000.


    Is an ad hoc message with formatting similar to the PRINTF format style used in C. The error message can have up to 400 characters. If the message contains more than 400 characters, only the first 397 will be displayed and an ellipsis will be added to indicate that the message has been cut. All ad hoc messages have a standard message ID of 14,000.

SQL Server 2000 Notes Chapter 12, Volume C Page 2 of 3

    This format is supported for msg_str:

    % [[flag] [width] [precision] [{h | l}]] type

    The parameters that can be used in msg_str are:

    {h | l} type

    Is used with character types d, i, o, x, X, or u, and creates short int (h) or long int (l) values.

    Character type Represents

    d or I Signed integer

    o Unsigned octal

    p Pointer

    s String

    u Unsigned integer

    x or X Unsigned hexadecimal


    Is the user-defined severity level associated with this message. Severity levels from 0 through 18 can be used by

    any user. Severity levels from 19 through 25 are used only by members of the sysadmin fixed server role. For severity levels from 19 through 25, the WITH LOG option is required.

Caution Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the

    client connection is terminated after receiving the message, and the error is logged in the error log and the

    application log.


    Is an arbitrary integer from 1 through 127 that represents information about the invocation state of the error. A

    negative value for state defaults to 1.


    Is the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters; however, the total number of substitution parameters

    cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: int1, int2, int4,

    char, varchar, binary, or varbinary. No other data types are supported.


    Is a custom option for the error. option can be one of these values.

    Value Description

    LOG Logs the error in the server error log and the application log.

    Errors logged in the server error log are currently limited to a

    maximum of 440 bytes.

    NOWAIT Sends messages immediately to the client.

    SETERROR Sets @@ERROR value to msg_id or 50000, regardless of the

    severity level.

SQL Server 2000 Notes Chapter 12, Volume C Page 3 of 3

    RAISERROR('Danger Will Robinson, Error!', 1, 1)


Msg 50000, Level 1, State 50000

    Danger Will Robinson, Error!

RAISERROR('Here is an error with the string %s and the number %d', 1, 1, 'MyString', -98)


    Here is an error with the string MyString and the number -98

Custom Error Messages:

    The sp_addmessage stored procedure adds a new error message to the sysmessages table.


    sp_addmessage [ @msgnum = ] msg_id ,

     [ @severity = ] severity ,

     [ @msgtext = ] 'msg'

     [ , [ @lang = ] 'language' ]

     [ , [ @with_log = ] 'with_log' ] -- TRUE or FALSE

     [ , [ @replace = ] 'replace' ] -- for replacing an existing message

    to drop a custom error message: use sp_dropmessage

Report this document

For any questions or suggestions please email