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. 

The @@ERROR Function 

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 designate 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 

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 

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. 

Sample Code 

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! 

USE master

EXEC sp_addmessage

  50010, /*message id*/

  16, /*severity level*/

  'Error in stored procedure %s', /*message text*/

  'us_english', /*language*/

  'FALSE', /*log this error*/

   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. 

CREATE PROCEDURE spDemo

AS BEGIN

 SELECT TOP 10 * FROM AUTHORS

 IF @@ROWCOUNT < 11

  RAISERROR (50010,12,1,'Raise Error Demo')

END

Step 2. Execute the procedure. 

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. 

RAISERROR ('An error occurred because we are overworked and underpaid!',10,1)

Real-World Use of @@ERROR and RAISERROR 

Although common practice tells us that user validation goes on the front end, most database administrators (DBA) implement developer validation of the back -end. Experience has taught them that we cannot always be trusted to implement requirements correctly. Another valid reason would be that your database is being used by several different applications, and as an added precaution the DBA has decided to implement database-level validation. 

Step 1. Create the following procedure in the pubs database. 

CREATE PROCEDURE spDiscounts(@TYPE VARCHAR(40),

 @STORE CHAR(4),@LOW SMALLINT, @HIGH SMALLINT, @DISCOUNT NUMERIC(9,2))

AS BEGIN

IF @DISCOUNT > 7

 BEGIN

  RAISERROR ('You entered %d, the discount can not be greater than 7.', 10, 1, @DISCOUNT)

 END

ELSE

 BEGIN

  BEGIN TRANSACTION

  INSERT INTO DISCOUNTS(DISCOUNTTYPE, STOR_ID, LOWQTY, HIGHQTY, DISCOUNT)

  VALUES (@TYPE,@STORE,@LOW,@HIGH,@DISCOUNT)

  IF @@ERROR <> 0

   ROLLBACK TRANSACTION

  ELSE

   COMMIT TRANSACTION

  END

END

Step 2. Execute the following procedure. 

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.