Transact-SQL Improves Database Error-Handling

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, RAISERROR functions and Try…Catch block 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.
• Transact-SQL Improves Database Error-Handling - 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 <>

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.

Try…….Catch Block

A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.

A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct.

If there are no errors in the code that is enclosed in a TRY block, when the last statement in the TRY block has finished running, control passes to the statement immediately after the associated END CATCH statement. If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed back to the statement that called the stored procedure or fired the trigger.

GOTO statements cannot be used to enter a TRY or CATCH block. GOTO statements can be used to jump to a label inside the same TRY or CATCH block or to leave a TRY or CATCH block.

The TRY…CATCH construct cannot be used in a user-defined function.

Retrieving Error Information
In the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed:

ERROR_NUMBER() returns the number of the error.
ERROR_SEVERITY() returns the severity.
ERROR_STATE() returns the error state number.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

These functions return NULL if they are called outside the scope of the CATCH block. Error information can be retrieved by using these functions from anywhere within the scope of the CATCH block.

Errors Unaffected by a TRY…CATCH Construct

TRY…CATCH constructs do not trap the following conditions:

1. Warnings or informational messages that have a severity of 10 or lower.
2. Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
3. Attentions, such as client-interrupt requests or broken client connections.
4. When the session is ended by a system administrator by using the KILL statement.

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

1. Compile errors, such as syntax errors, that prevent a batch from running.
2. Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.