(Error Handling blocks in stored procedures depending on nested level)

Using Hierarchy of Stored Procedures we can generate hierarchical report of nested procedures:

If Proc1 calls Proc2 and Proc2 calls Proc3 we will have this report:

-Proc1
--Proc2
----Proc3

Classification of stored procedures depending on nested level:

First Level (FL), Procedure called by client application; First level procedure calls Middle level procedures and Last level procedures.

Middle Level (ML),  Nested procedure called by an other procedure, Middle level procedure calls Middle or Last level procedures.

Last Level (LL),  Nested procedure called by First or Middle level procedure; Last level procedure doesn’t call any other

procedure.

Error Handling blocks in stored procedures depending on nested level

Last Level Procedure

CREATE PROCEDURE Proc111 AS
  UPDATE Table1 …
  SET @Err = @@ERROR
  IF @Err <> 0
 BEGIN 
[RAISERROR/XP_LOGEVENT]
RETURN (@Err)
 END

Error handling block in last level procedure has to be included after every statement that can generate error, which can cause mistakes in following execution. We are checking the system function @@Error and if it is not equal to zero we break execution and return @@Error value to the procedure caller.

Depending on programming logic we can use RAISERROR to send error to client or XP_LOGEVENT to write message error in log. This is good way to exactly specify error location. But remember that error-handling blocks has to be as simple as possible, because if error occurred in error handling block we can’t catch it. There is no sense to implement error handling in error handling blocks.

Middle Level Procedure

CREATE PROCEDURE Proc11 AS
  UPDATE Table1 
  
  SET @Err = @@ERROR
  IF @Err <> 0
 BEGIN
[RAISERROR/XP_LOGEVENT]
RETURN (@Err)
 END
  
  EXEC @Ret = Proc111
  
  SELECT @Ret = coalesce(nullif(@Ret, 0),@@error,1001)
  IF @Ret <> 0
BEGIN
[RAISERROR/XP_LOGEVENT]
RETURN (@Ret)
END

Procedure has the same block after individual statements and second block after nested procedure execution. COALESCE function returns the first non-null expression between its arguments. Using COALESCE function I' m checking Return parameter and @@Error function in same time. If @Ret has value different than 0 or null, we are sure that the procedure returns value, which means the procedure is not executed correctly.

Programmers usually use one @ret variable to catch return parameters for all called procedures in one procedure, so we are not sure that the procedure is executed OK if first procedure is executed OK, but second procedure causes scope abortion error and Return parameter is not affected after calling second procedure. Therefore, if @Ret is zero we convert it to null and additionally check up the system function @@Error. If @@Error has a value different than zero then we will set @Ret to the @@Error value (scope level error - more about this kind of errors I described in article Be

Prepared part 1).

If we want to be 100% sure that COALESCE will return value we can use constant like third parameter (1001 in the sample). This can happen only with Remote procedure calls.

First level procedure 

CREATE PROCEDURE Proc1 AS
  
  BEGIN TRANSACTION
  
  UPDATE Table1 …
  
  SET @Err = @@ERROR
  IF @Err <> 0
 BEGIN
ROLLBACK TRANSACTION
[RAISERROR/XP_LOGEVENT]
RETURN (@Err)
 END
  
  EXEC @Ret = Proc11
  
  SELECT @Ret = coalesce(nullif(@Ret, 0),@@error,1001)
  IF @Ret <> 0
BEGIN
ROLLBACK TRANSACTION
[RAISERROR/XP_LOGEVENT]
RETURN (@Ret)
END
  
  COMMIT TRANSACTION

First level procedure has the same blocks after individual statements and procedure executions. Additionally First level procedures are responsible for managing transactions. Transactions BEGIN and COMMIT in First level procedure. If error is detected in error handling block it will issue ROLLBACK transaction. In this model transactions can be long. That is not good for database performance. We have to use short procedures in this model. Otherwise we have to implement sharing in transaction management between procedures.

Several Considerations

Getting native error versus keeping error location

Using return command we can scale up return parameter from last level procedure to client’s application. In this model, native error number over Return command is sent like a return parameter to the client’s application. But we can lose error statement location. We can use RAISERROR or XP_LOGEVENT to inform client or to write in log information about error number, location and other user defined elements. In other way we can use user-defined return parameters for every location and we can exactly know where error has occurred just using Return parameter.

We have two important information (error and location), but only one return parameter from every procedure. It’s not sound good to use Output parameters in this purpose. So we have three options, to return native error, to return statement location number or create one return parameter which will contain combination of error number and statement location number. 

What if Error occurs in error handling block?

There is no sense to put error-handling block in error handling block. It is a termless process. So there is no way to catch error in error handling blocks. That is way, it is most important to write short, clear, well-standardized and good tested error handling blocks,

To log or not to log?

We could use XP_LOGEVENT to write to log additional information about error. But in same time it is most important to write short and simple error handling blocks. So ROLLBACK and RETURN is enough to manage database consistency and inform client about error. But if it is so important for your database you can write to log some additional information about error (location, time, context, active user etc.)

RAISERROR

User, Application support employees and Developers have to have a clear message about error, but not all of them in the same format. User doesn’t have to know that same cursor is declared two times, but he has to know that action isn’t performed. The application supporters have to know more about type of error, location and they will try to correct the problem. Sometimes developer’s action is needed. They have to know as much as possible about error. The time needed for correcting is depending on information about error. So I’m not RAISERROR admirer. RAISERROR is not so good in security aspect. Native error messages can discover a lot about database object and facilitate SQL injection. 

Controlling transactions in first level procedures

As we can see I put the BEGIN, COMMIT and ROLLBACK just in error handling blocks in First Level Procedure. It will avoid potential orphan nested transactions, and will standardize transaction management. Every ROLLBACK will reverse all transaction, so we don’t need nested ROLLBACKS. What is most important is to issue ROLLBACK as soon as possible after error and free all locked resources. In this model in case of error process immediately return parameter to parent procedure and quickly go up to First Level Procedure and issues ROLLBACK. This is not immediately ROLLBACK, but I think we can do several RETURN commands, for good transaction management.

How to trap errors in user defined functions?

User defined functions are invoked as a part of SQL query.

RETURN command in function has totally different meanings that RETURN in stored procedure. The worst of all, the system function @@Error has a value 0; even error happen in last executed statement in function. So it is recommended to write short and clear functions, to test before release, and use stored procedure instead of functions everywhere when it is possible.

Is it possible to make bullet-proof stored procedure?

SQL Server is unable to cover all possible errors. There is no sense to write error-handling block after every statement. The model has to prevent incorrect data and help defining more efficient strategy for correcting errors. Discovering, localizing and investigating error is only first step. Depending on first step we can change the code and correct mistakes.