Handling Errors in Stored ProceduresBy Garth Wells on 5 February 2001 | Tags: Stored Procedures The following article introduces the basics of handling errors in stored procedures. If you are not familiar with the difference between fatal and non-fatal errors, the system function @@ERROR, or how to add a custom error with the system stored procedure sp_addmessage, you should find it interesting.
The examples presented here are specific to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure, the best you can do (assuming it’s a non-fatal error) is halt the sequential processing of the code and either branch to another code segment in the procedure or return processing to the calling application. Notice that the previous sentence is specific to non-fatal errors. There are two type of errors in SQL Server: fatal and non-fatal. Fatal errors cause a procedure to abort processing and terminate the connection with the client application. Non-fatal errors do not abort processing a procedure or affect the connection with the client application. When a non-fatal error occurs within a procedure, processing continues on the line of code that follows the one that caused the error.
The following example demonstrates how a fatal error affects a procedure.
--Results--
The SELECT in the procedure references a table that does not exist, which produces a fatal error. The procedure aborts processing immediately after the error and the PRINT statement is not executed. To demonstrate how a non-fatal error is processed, I need to create the following table.
This example uses a procedure to INSERT a row into NonFatal, but does not
include a value for Column2 (defined as NOT NULL).
--Results--
The last line of the results (shown in blue) demonstrates that the error did not affect the processing of the procedure—the PRINT statement executed. You might be wondering what actions cause fatal errors. Unfortunately, the actions that cause a fatal error are not well documented. Each error has an associated severity level that is a value between 0–25. The errors with a severity level of 20 or above are all fatal, but once you get below this value there is no well-defined rule as to which errors are fatal. In truth, though, worrying about which errors are fatal is a bit useless because there is no code you can implement that will allow you to handle them gracefully. Of course, you can use pro-actice coding to make sure fatal-errors do not occur. For example, if your application allows users to type in the name of the table on which a query is based you can verify it’s existence before referencing it with dynamic SQL. @@ERRORThe @@ERROR system function is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client’s connection. When a statement executes successfully, @@ERROR contains 0. To determine if a statement executes successfully, an IF statement is used to check the value of the function immediately after the target statement executes. It is imperative that @@ERROR be checked immediately after the target statement, because its value is reset when the next statement executes successfully. Let’s alter ps_NonFatal_INSERT to use @@ERROR with the following.
--Results--
When an error occurs, the PRINT statement produces the "Error Occurred" message. The following code shows the results of a valid call to ps_NonFatal_INSERT.
--Results--
The last line of the results (in blue) indicates the PRINT statement executed as expected. RAISERRORThe RAISERROR statement is used to produce an ad hoc error message or to retrieve a custom message that is stored in the sysmessages table. You can use this statement with the error handling code presented in the previous section to implement custom error messages in your applications. The syntax of the statement is shown here.
A description of the components of the statement follows.
The number of options available for the statement make it seem complicated, but it is actually easy to use. The following shows how to create an ad hoc message
with a severity of 10 and a state of 1.
--Results--
The statement does not have to be used in conjunction with any other code,
but for our purposes it will be used with the error handling code presented earlier.
The following alters the ps_NonFatal_INSERT procedure to use RAISERROR.
When an error-producing call is made to the procedure, the custom message is
passed to the client. The following shows the output generated by Query Analyzer.
--Results--
The output may seem confusing because we still see the same error message displayed before we started using RAISERROR. The custom error (in blue) is also displayed. The output is a function of Query Analyzer and we cannot control its behavior. When you develop client applications you will have control over what is displayed to the end user so the output will be less confusing. Adding a Permanent Custom MessageIf you have a message that is going to be used frequently, it is more efficient to add it to the sysmessages table and reference it by its unique ID. The system stored procedure sp_addmessages adds an error message to sysmessages. The following shows how to add a new error message.
--Results--
Note that the ID for a custom message must be greater than 50,000. The new message can be accessed with RAISERROR using the following.
--Results--
In a forthcoming article I will show you how to access a custom error using the Errors collection of the ADO Connection Object. This approach allows you to control the type of error messages presented to your end-users. Garth
|
- Advertisement - |