Lesson 9 | Stored Procedure Error Handling |
Objective | Describe how to implement error handling in your stored procedures. |
Stored Procedure Error Handling
Stored procedures can be particularly susceptible to errors because of the following factors:
- 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.
- 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
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.
Handling errors
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:
- Doing nothing
- Raising an error
- Exiting the stored procedure
Raising an error
The following Carousel detects and raises an error that has occurred in a stored procedure:
Exiting the 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
This is an example of how you would use the RETURN
statement:
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.