Server Interaction  «Prev  Next»

Lesson 6Raising errors
ObjectivePractice handling errors in your Transact-SQL code.

Handling Errors SQL- Server

Transact-SQL Code | Raising Errors

In the previous lesson, you learned about system errors, which are generated automatically by SQL Server.
In some cases, you will want to generate an error and return it back to a calling program. For example, stored procedures, which you will learn about later in this course, often require you to return an error back to the program that called the stored procedure.
These types of errors are called user-defined errors. There are two types of user-defined errors:
  1. User-defined static error: A user-defined message that exists in the sysmessages system catalog. These errors can be raised by your Transact-SQL statements. User-defined messages are added to the sysmessages system catalog by using the sp_addmessage system stored procedure.
  2. User-defined dynamic error: A user-defined message whose DESCRIPTION is generated dynamically and does not reside in the sysmessages system catalog. This type of error can be raised by your Transact-SQL statement.

User-defined static error

To raise an error, use the RAISERROR function, as shown below:
ID is the error number that exists in the system catalog. All system errors will have an ID less than 50000. (option #1 above) These errors exists in the system catalog, but cannot be raised with the RAISERROR Transact-SQL statement.
1) ID is the error number that exists in the system catalog. All system errors will have an ID less than 50000. (option #1 above) These errors exists in the system catalog, but cannot be raised with the RAISERROR Transact-SQL statement.
Message is the user-defined error text to be returned as the error text (option #3 above). If you are not going to raise an error by number.
2) Message is the user-defined error text to be returned as the error text (option #3 above). If you are not going to raise an error by number.
Severity is the level that defines the category of errors. Severity can be a value from 0 to 25. For user-defined error messages, the meaning of the severity is defined by the SQL programmer. However, all messages with a severity of 20-25 are considered to be fatal errors, which will terminate the SQL Server connection when they occur. A fatal error is one that is so critical to the batch or stored procedure that processing cannot continue. You must use with the WITH LOG option if you specify a severity between 19 and 25.
3) Severity is the level that defines the category of errors. Severity can be a value from 0 to 25. For user-defined error messages, the meaning of the severity is defined by the SQL programmer. However, all messages with a severity of 20-25 are considered to be fatal errors, which will terminate the SQL Server connection when they occur. A fatal error is one that is so critical to the batch or stored procedure that processing cannot continue. You must use with the WITH LOG option if you specify a severity between 19 and 25.
State allows you to further define how the error was generated. If desired this can be a number from 1 to 127
4) State allows you to further define how the error was generated. If desired this can be a number from 1 to 127
Argument is used only if the Message contains variables. If it does, Argument to specify the values for those parameters.
5) Argument is used only if the Message contains variables. If it does, Argument to specify the values for those parameters.
Option can be one or more of the following: 1) LOG - Logs the error in the application log for storage., 2) NOWAIT - sends the error to the calling application immediately, 3) SETERR
6) Option can be one or more of the following: 1) LOG - Logs the error in the application log for storage., 2) NOWAIT - sends the error to the calling application immediately, 3) SETERR

The following Transact-SQL statement is used to raise a dynamic error with a severity level of 16 and a state of 1: 
RAISERROR ('An error occurred', 16, 1)

This Transact-SQL statement raises a fatal error and writes it to the application error log: 
RAISERROR ('An error occurred', 20, 1) WITH LOG

This Transact-SQL statement raises error number 50001, which must exist in the sysmessages system catalog: 
RAISERROR (50001, 16, 1)

In the next lesson, the information covered in this module will be reviewed.

Raising Errors - Exercise

Click the Exercise link below to practice raising errors.
Raising Errors - Exercise