Post
Ask Question

Handling Errors within Stored Procedures in SQL Server

Overview 

 
The robust Transact-SQL (T-SQL) syntax in SQL Server provides developers with an efficient way to handle errors within stored procedures. This article discusses the @@ERROR, SP_ADDMESSAGE, and RAISERROR functions within SQL Server. 
 

@@ERROR Function in SQL Server

 
Upon the completion of any T-SQL statement, SQL Server sets the @@ERROR object. If the statement was successful, @@ERROR is set to 0, otherwise it is set to the designated error code. All SQL Server error codes can be found within the master.dbo.sysmessages system table. One important thing to remember is that @@ERROR is cleared each time a statement is executed. It is a good practice to store the value within a local variable. 
 
Anatomy of an Error 
 
All errors raised by SQL Server return the following information. 
  • Number - Each error has a unique number assigned to it. 
  • Message - Contains information about the error. Many errors have substitution variables that can be placed within the text. We will cover this in the SP_ADDMESSAGE and RAISERROR sections. 
  • Severity - Indicates how serious the error is. The values are between 1 and 25. 
  • State - As quoted from SQL Server books on line: "Some error codes can be raised at multiple points in the source code for SQL Server. For example, an 1105' error can be raised for several different conditions. Each place the error code is raised assigns a unique state code. A Microsoft support engineer can use the state code from an error to find the location in the source code where that error code is being raised, which may provide additional ideas on how to diagnose the problem." 
  • Procedure name - If the destruction occurred within a stored procedure, the name is returned. 
  • Line - The line number of the demon code. 
There are two classes of error messages in SQL Server -- fatal and nonfatal. Remember that fatal errors will kill the client connection. Creating stored procedures that cause fatal errors is a good way to get rid of slacking programmers or consultants. 
 

SP_ADDMESSAGE in SQL Server

 
Use the sp_addmessage feature to add your own customized messages. The following information is given about each parameter: 
  • @msgnum (smallint) - This is the message ID. All user-defined messages start with 50001. The combination of this parameter and language must be unique. 
  • @severity (smallint) - The severity level is between 1 and 25. Only the system administrator can add a message above the level of 18. Messages below the age of 18 are still considered to be "toddlers," and anyone can add them. 
  • @msgtext nvarchar(255) - This is the text of the error message. Variables can be used within the text. This functionality is similar to the printf feature within C. 
  • @lang - Since SQL Server can support different languages, you can store your error messages in multiple languages. This will help greatly when we start joint-developing with people from different planets. If left NULL, it will default to the default language of the current session. 
  • @with_log varchar(5) - This value can be TRUE or FALSE. If you set this parameter to "TRUE," then messages are written to the Windows and SQL Server application log. 
  • @replace varchar(7) - This allows you to replace an existing error message with a new message text and severity level. The default is NULL. This is a great way to tick the database administrator off! 

RAISERROR in SQL Server

 
You can also use the RAISERROR command to create an error message. RAISERROR can send the error message information back to a client application. 
 
The following information is given about the parameters. 
  • @msg_id - This is the message ID of your user-defined message. All adhoc error messages are given the message ID of 50000. 
  • @msg_str - The 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. You can also use formatting values within the message text. An example of this is given below. 
  • @argument - These are values to be used within the message text. 
  • @WITH - Acceptable values are LOG, NOWAIT, and SETERROR. The LOG option logs the error to the server and application log; NOWAIT sends the messages immediately back to the client application; and SETERROR sets the @@ERROR value to the @MSG_ID or 50000, regardless of the serverity level. 
The User-defined Error Message 
 
For the formatting, I will use %s for strings and %I for integers. This functionality is similar to the printf function in C. I know you still have those C notes from college somewhere! 
  1. USE master  
  2. EXEC sp_addmessage  
  3.   50010, /*message id*/  
  4.   16, /*severity level*/  
  5.   'Error in stored procedure %s', /*message text*/  
  6.   'us_english', /*language*/  
  7.   'FALSE', /*log this error*/  
  8.    NULL /*replace existing error*/  

Using RAISERROR to Call the Error Message 


I will use the pubs database for this demonstration. 

Step 1. Create the following procedure.
  1. CREATE PROCEDURE spDemo  
  2. AS BEGIN  
  3.  SELECT TOP 10 * FROM AUTHORS  
  4.  IF @@ROWCOUNT < 11  
  5.   RAISERROR (50010,12,1,'Raise Error Demo')  
  6. END  
Step 2. Execute the procedure.
  1. Exec spDemo  
You will then get the following error message. 
 
"Server: Msg 50010, Level 12, State 1, Procedure spDemo, Line 5
Error in stored procedure Raise Error Demo"
 
Here is another example of using RAISERROR without a user-defined message. 
  1. RAISERROR ('An error occurred because we are overworked and underpaid!',10,1)  

Example of @@ERROR and RAISERROR 

Step 1. Create the following procedure in the pubs database. 
  1. CREATE PROCEDURE spDiscounts(@TYPE VARCHAR(40),  
  2.  @STORE CHAR(4),@LOW SMALLINT, @HIGH SMALLINT, @DISCOUNT NUMERIC(9,2))  
  3. AS BEGIN  
  4. IF @DISCOUNT > 7  
  5.  BEGIN  
  6.   RAISERROR ('You entered %d, the discount can not be greater than 7.', 10, 1, @DISCOUNT)  
  7.  END  
  8. ELSE  
  9.  BEGIN  
  10.   BEGIN TRANSACTION  
  11.   INSERT INTO DISCOUNTS(DISCOUNTTYPE, STOR_ID, LOWQTY, HIGHQTY, DISCOUNT)  
  12.   VALUES (@TYPE,@STORE,@LOW,@HIGH,@DISCOUNT)  
  13.   IF @@ERROR <> 0  
  14.    ROLLBACK TRANSACTION  
  15.   ELSE  
  16.    COMMIT TRANSACTION  
  17.   END  
  18. END  
Step 2. Execute the following procedure. 
  1. exec spDiscounts 'My Discount', NULL, 10, 100, 12  
You will receive the following error message: 
 
"You entered 12; the discount cannot be greater than 7."
 
Returning logical error messages like this to the client application will save hours of head scratching. 
 

Summary

 
I hope I have spawned a few ideas of what you can do with @@ERROR, SP_ADDMESSAGE, and RAISERROR. T-SQL gives developers a very powerful tool to use when creating stored procedures. You can find well-written documentation within the SQL Server books on line. If you have any problems, questions, or comments about this article please email me. 


Similar Articles