Stored Procedure Error Handling

Stored procedures can be particularly susceptible to errors because of the following factors:

1. Code volume and complexity: Because a stored procedure generally consists of many Transact-SQL statements, there is a very real possibility that an error will occur. Also, the more complex a Transact-SQL statement gets, the more chance there is for error.
2. Location: Stored procedures are compiled on the server. This means that another SQL programmer could drop one of the tables referenced by your stored procedure without your knowledge. Even if you didn’t have an error before you executed the stored procedure, you sure do now.

Are you convinced you that errors can occur?

Detecting errors is quite simple. Use the global function @@error directly after a Transact-SQL statement. If @@error contains a value of 0, then all is fine. If @@error contains a value other than 0, then an error occurred in the last statement.

Once an error has been detected, you can decide how to handle it. Handling errors is nothing more than detecting that an error occurred and dealing with it in some way. For example, you might not care that an error occurred, and processing can continue. On the other hand, if an error occurs, especially at a given point in the stored procedure, you must abort because continuing processing would yield invalid data. This is generally known as a fatal error .
When an error occurs, you have several options, including:

1. Doing nothing
2. Raising an error
3. Exiting the stored procedure

The following Carousel detects and raises an error that has occurred in a stored procedure:

To exit from a stored procedure unconditionally and immediately, you use the RETURN Transact-SQL statement. Following the RETURN statement, you can specify code that your calling procedure can check. This code has no significance to SQL Server 2012. The return code is significant only to you. For example, this statement exits a stored procedure with a value of 0:

RETURN 0

CREATE PROCEDURE usp_GetLastName @EmployeeID int
AS
SELECT LastName 
FROM employees  
WHERE EmployeeID = @EmployeeID   
IF @@error <> 0 
BEGIN     
RAISERROR (‘Error Occurred’, 16, 1) 
RETURN 1  
END 
ELSE 
RETURN 0

In the code above, if no error occurs, the stored procedure returns with a status of 0. Otherwise, the stored procedure returns with a status of 1. In the next lesson, you will learn about and practice using output parameters.