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