Error Handling

Error handling is a simple, yet often overlooked, component of good stored procedures. Effective error handling allows us, as the database developer, to inform the client tools of any failure in the application or database logic. Notice the difference between these two types of logic:

• Application logic
  The logic is mainly associated with our business/application rules that we explicitly check within the code.

• Database logic
  The logic is associated with the retrieval and modification of rows within the database tables. We may not explicitly check the database logic, however we should respond to error conditions raised by SQL Server.

The Table1_INSERT example we used in the previous section follows application logic, as this relates to the business rules surrounding our application. It could be modified as:

     CREATE PROCEDURE Table1_INSERT     ...         INSERT dbo.Table1(IDCol,VarCharCol)         VALUES(@IDValue, @VarCharValue)       END       ELSE       BEGIN         -- Some form of application error is generated         -- to inform the end user that the INSERT         -- request failed.       END 

Failure of database logic is a somewhat easier to handle, for example an attempt to insert rows with duplicate primary keys is a failure of database logic:

     CREATE TABLE #PK_Test     (       IDCOL int PRIMARY KEY     )     INSERT #PK_TEST VALUES(1)     INSERT #PK_TEST VALUES(1)     DROP TABLE #PK_Test 

Even through an error is generated in the INSERT statement above the code continues to run and the table is dropped with the next statement. This is because the violation of a constraint does not generate an error severe enough to halt code execution.

Not all database logic errors require checking. Only those which can be thought of as "soft errors", or errors that occur at run time and do not halt the execution of code, require checking. Other errors generated by SQL Server which result in the execution of code being halted will inform the calling application of the error condition automatically. These errors cannot be trapped or handled from within a stored procedure, as SQL Server does not support exception handling from within T-SQL.

The way we check for these soft errors is by using the @@ERROR global variable.

@@ERROR

@@ERROR gives the error condition of the last SQL command. If the last command was successful this will be 0; otherwise, this will be a non-zero value. Using @@ERROR we can determine the error number of the error that occurred when the last command was executed, but not the severity number of that error.

There are a couple of approaches to using @@ERROR; the most common one is to check and handle the error condition immediately after the statement that generated the error. An alternative is to handle the error in a stored procedure "catch all" error logic routine.

For example, if you handle the @@ERROR value after each statement, you would use error handling code like:

     CREATE PROCEDURE ErrorDemo       @Key1    INT,       @Key2    INT,       @Key3    INT,       @Value1  VARCHAR(255),       @Value2  VARCHAR(255),       @Value3  VARCHAR(255)     AS     SET NOCOUNT ON     UPDATE Table1 Set Col2=@Value1 WHERE Col1=@Key1     IF @@ERROR<>0     BEGIN       PRINT 'Handle error here'     END     UPDATE Table2 Set Col2=@Value2 WHERE Col1=@Key2     IF @@ERROR<>0     BEGIN       PRINT 'Handle error here'     END     UPDATE Table3 Set Col2=@Value3 WHERE Col1=@Key3     IF @@ERROR<>0     BEGIN       PRINT 'Handle error here'     END 

To handle the error more generically, we could rewrite this procedure as:

     ALTER PROCEDURE ErrorDemo       @Key1    INT,       @Key2   INT,       @Key3    INT,       @Value1  VARCHAR(255),       @Value2  VARCHAR(255),       @Value3  VARCHAR(255)     AS     SET NOCOUNT ON     DECLARE @ErrorValue INT     UPDATE Table1 Set Col2=@Value1 WHERE Col1=@Key1     IF @@ERROR<>0       SELECT @ErrorValue=@@ERROR     UPDATE Table2 Set Col2=@Value2 WHERE Col1=@Key2     IF @@ERROR<>0       SELECT @ErrorValue=@@ERROR     UPDATE Table3 Set Col2=@Value3 WHERE Col1=@Key3     IF @@ERROR<>0       SELECT @ErrorValue=@@ERROR     IF @ErrorValue<>0     BEGIN       PRINT 'Generically handle error'     END 

The difference between these procedures is that the first one requires more code to handle errors, but we can check each statement for an error and raise a specific error message for our client application. One the other hand, the second method requires less code; however, if one or more errors took place and we do not know which statement caused the error, we can only raise a generic error message in our client application.

The command we use to raise error messages in our client applications is RAISERROR (note the missing E between RAISE and ERROR).

RAISERROR & Return Parameters

RAISERROR, manually, throws an error condition back to the client application that is executing commands against SQL Server. It is has the following syntax:

     RAISERROR ( { msg_id | msg_str } { , severity , state } , argument ) 

While there are other ways of passing errors back to our client application, such as the return parameter of our stored procedure, RAISERROR is more convenient. It usually causes the client application's error handler to be invoked to deal with the situation (for example, causing an exception to be thrown in the .NET Framework). RAISERROR is used to throw an error condition back to our client application for an unexpected situation, and it includes as much information as possible to help the user or the application in determining the cause of the problem.

Return parameters on stored procedures are another way of passing a failure indication back to our client application, but these really serve a different purpose. They don't cause the client application's error handler to be invoked. They are usually used to indicate the success or failure of a stored procedure based on a set of known or expected conditions – usually 0 for success, and 1 and above for "expected" failure situations that can be interpreted by client applications.

Important

RAISERROR and return parameters are not mutually exclusive. Normally, RAISERROR is used to indicate that an unexpected error has occurred and return parameters are used to indicate an expected failure condition.

The following is an example of using RAISERROR within a stored procedure:

     CREATE PROCEDURE RAISERRORTest       @IDCol int     AS       DECLARE @ErrorString VARCHAR(8000),               @ErrorValue INT       IF OBJECT_ID('dbo.RAISERRORTestTable') IS NULL       BEGIN         CREATE TABLE dbo.RAISERRORTestTable         (           IDCOL int PRIMARY KEY         )     END     INSERT dbo.RAISERRORTestTable(IDCol) VALUES(@IDCol)     SELECT @ErrorValue = @@ERROR     IF @ErrorValue<>0     BEGIN       SELECT @ErrorString='The following error has occured: ' +                            CAST(@ErrorValue AS VARCHAR(8))       RAISERROR( @ErrorString,16,1)     END 

Now, execute the following statements:

     EXEC RAISERRORTest 1     EXEC RAISERRORTest 1 

The first statement executes successfully, while the second one causes a primary key violation. This violation generates an informational message, which appears within our Query Analyzer window:

     Server: Msg 2627, Level 14, State 1, Procedure RAISERRORTest, Line 16     Violation of PRIMARY KEY constraint 'PK__RAISERRORTestTab__5BE2A6F2'.     Cannot insert duplicate key in object 'RAISERRORTestTable'. 

However, as code execution continues the manual error is also raised, which can be caught within the application's error handling routines because of its higher severity:

     Server: Msg 50000, Level 16, State 1, Procedure RAISERRORTest, Line 19     The following error has occured: 2627