Introduction

Does your stored procedure execution stop at the point it encounters an error during run time? Or does it disregard the error and keep executing the remaining code in the SP? Should it keep executing? Should it stop? How can you control the way your SP's handle errors? Read on if you are interested in taking a look at these questions and their answers.

Stored Procedures That Stop When An Error Occurs

A stored procedure that only has static SQL queries in it could choke on the first error it encounters at run time and stop. In most, if not all, cases you would want the SP to stop execution on finding an error. The following SP should be created in the pubs database and then executed:

CREATE PROCEDURE spStaticStopOnFirstError AS
SELECT * FROM NonExistantTable
SELECT * FROM authors

You will find that the query to get all rows from the author table is never reached since the table called NonExistantTable can't be found.

Stored Procedures That Don't Stop When An Error Occurs

You will find that not all SP's will stop upon finding an error. Some will simply give you an error message and go on to the next query. The first of these is similar to the above SP. I modified it so that it is now executing queries stored in a variable; a dynamic SP:

CREATE PROCEDURE spDynamicNoStopOnFirstError AS
DECLARE @Query varchar(300)
SET @Query = 'SELECT * FROM NonExistantTable'
EXEC (@Query)
SET @Query = 'SELECT * FROM tblText'
EXEC (@Query)

You will notice that this SP executes the exact same code as the one above called spStaticStopOnFirstError. However, this one did not stop when it discovered the table NonExistantTable does not exist. This is because each time you execute a string it is treated as a separate batch (or scope) and so in this case the error it causes isn't treated the same as it was for the static SP in my previous example.

Here is an example of a static stored procedure that will not stop when an error is encountered:

CREATE PROCEDURE spStaticNoStopOnFirstError AS
UPDATE authors SET au_id = 'Do not Copy'
WHERE au_id IN ('172-32-1176', '213-46-8915')
INSERT INTO authors (au_id, au_lname, au_fname, Phone, Contract)
SELECT LEFT(au_id, 10) + '0', 'delete', 'delete', 'delete',Contract
FROM authors WHERE au_id <> 'Do not Copy'

In the above example the results of the second query depend on the successful execution of the first query. Since the first query fails and the SP continues executing it inserts 2 more rows than what was intended. The desired result would be to halt execution if the first query fails. I'll show you how in the next section.

The last instance I want to show here is a nested stored procedure:

CREATE PROCEDURE spNestedNoStopOnFirstError AS
EXEC spStaticStopOnFirstError
EXEC spDynamicNoStopOnFirstError

This SP executes two of the SPs described in previous examples. The first one is the one that halts upon encountering the first error and the second one does not. The spNestedNoStopOnFirstError will execute both SPs in spite of the errors.

Handling Errors During Execution

@@ERROR plays a key role in handling errors. If an error occurs after a query is executed then @@ERROR will have the error number of that error. Its value will be 0 if there was no error. Using it you will be able to detect an error and use that info to influence the reaction of your SP. You must check @@ERROR before executing the next query or the value it has will be overwritten with the value for the query that was just executed.

For any query that halts the execution of a stored procedure you will not be able to get a value from @@ERROR inside that SP. Try adding SELECT @@ERROR to the SP called spStaticStopOnFirstError between the two SELECT queries and execute it. You will get no error number because execution halts before that SELECT statement is reached. However, if you execute SELECT @@ERROR in the same query window where you executed spStaticStopOnFirstError you will get the error number.

Here is an example inspired by the request of Janko Lupsa, one of the many who read my article Nesting Stored Procedures. In this SP I will show you how to influence the reaction of a top level nested stored procedure upon encountering an error in an SP it executes and one method of how to display the name of the SP that executes with errors:

CREATE PROCEDURE spNestedErrorHandling AS
DECLARE @Error varchar(5)
EXEC spStaticStopOnFirstError
SET @Error = @@ERROR
IF @Error <> 0
BEGIN
PRINT 'spStaticStopOnFirstError execution failed with error ' + @Error
RETURN
END
EXEC spDynamicNoStopOnFirstError

The error handling I have placed in the above stored procedure does two things. First, it tells you the name of the SP that generated an error and the error number. Second, I used the RETURN command to halt execution.

You will notice that the first thing I do after executing spStaticStopOnFirstError is save the value of @@ERROR into a local variable. You can make the IF statement work with @@ERROR however when you PRINT the error number you will find that it is 0. Saving @@ERROR to a local variable ensures that no other line of code (in this case the IF statement) resets the value of @@ERROR before you display it on the screen.

Conclusions

In this article I have shown how some stored procedures stop upon encountering an error during execution and how others don't. I've described how nesting SP's will give you control over what happens when an error is encountered. Allow me to offer my thanks to Janko Lupsa for his question that helped me make this article more useful than it otherwise might have been.