This blog is part of the series The TSQL Classes: Stored Procedure
The link to previous post are given below.
https://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedures-a-primer
https://www.sqlservergeeks.com/blogs/AhmadOsama/personal/787/the-tsql-classes-stored-procedures-parameter-sniffing-resolution
In this class I will talk about ways to handle error in a stored procedure.
Error/Exception handling is an important part of programming whether it’s T-SQL /.Net. There are two main methods to handle errors in stored procedures.
- @@ERROR
- TRY and CATCH Block
@@Error Global Variable
The @@ERROR (Global variable) returns the error number for the last Transact-SQL statement executed. This can be used in IF / ELSE block to check if error occurred and take appropriate action accordingly. The value of @@error gets reset after every statement. Thus it’s advisable to have it stored in a variable.
Let’s see how @@ERROR is used to check errors.
-- Drop procedure if exists
IF Object_ID('uspAddCurrency') IS NOT NULL
BEGIN
DROP PROCEDURE uspAddCurrency
END
GO
CREATE PROCEDURE uspAddCurrency
@CurrencyCode nChar(3),
@Name nvarchar(50)
AS
BEGIN
-- Insert a new record into currency table
INSERT INTO [Sales].[Currency]
([CurrencyCode]
,[Name]
,[ModifiedDate])
VALUES
(
@CurrencyCode,
@Name,
GetDate()
)
-- return error number
-- this is same as returned in Message tab
SELECT [ErrorNumber]=@@ERROR
END
The above procedure inserts a value in Sales.Currency table. It then checks the value in @@Error global variable. A nonzero value means an error and a value of 0 indicates success. Let’s now execute the procedure and observe value returned by @@Error.
The Sales.Currency table has a primary key constraint on CurrencyCode column which means that it can’t contain duplicate values. The procedure is thus executed twice to force a primary key violation error. The first execution of the procedure inserts a record successfully in Sales.Currency table and thus @@Error returns a value of 0, where as the second execution fails with a primary key violation error and returns error number 2627, which is equivalent to the value returned in error under message tab (highlighted in yellow).The procedure can further modified to return custom message or specific return value by checking the value in @@Error variable.
IF Object_ID('uspAddCurrency') IS NOT NULL
BEGIN
DROP PROCEDURE uspAddCurrency
END
GO
CREATE PROCEDURE uspAddCurrency
@CurrencyCode nChar(3),
@Name nvarchar(50)
AS
BEGIN
-- Insert a new record into currency table
DECLARE @ErrorNumber int
INSERT INTO [Sales].[Currency]
([CurrencyCode]
,[Name]
,[ModifiedDate])
VALUES
(
@CurrencyCode,
@Name,
GetDate()
)
-- return error number
-- this is same as returned in Message tab
SELECT @ErrorNumber=@@ERROR
IF(@ErrorNumber<>0)
BEGIN
Print 'Error Occurred!!!'
-- Non Zero value indicates failure
return 99
END
ELSE
BEGIN
Print 'Value Inserted Successfully'
-- 0 indicates success
return 0
END
END
In above procedure, the value returned by @@Error is assigned to a variable because its value is reset after every statement.
Try and Catch
BOL defines Try and Catch as
“It Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.”
Let’s modify the procedure uspAddCurrency discussed above to use Try and Catch to catch error instead of @@Error global variable.
IF Object_ID('uspAddCurrency') IS NOT NULL
BEGIN
DROP PROCEDURE uspAddCurrency
END
GO
CREATE PROCEDURE uspAddCurrency
@CurrencyCode nChar(3),
@Name nvarchar(50)
AS
BEGIN
-- SQL Statement goes in Try block
BEGIN TRY
-- Insert a new record into currency table
INSERT INTO [Sales].[Currency]
([CurrencyCode]
,[Name]
,[ModifiedDate])
VALUES
(
@CurrencyCode,
@Name,
GetDate()
)
END TRY
-- Error is handled in Catch block
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
Let’s execute the procedure and analyze the result.
The procedure errors out on second execution with a primary key violation error and control goes to catch block. The catch block returns each and every detail of the error occurred. In this case, there is no sql server error shown under message tab which was shown in case of @@Error variable.
Another important use of Try and Catch is error logging. The error returned can be logged to a table for troubleshooting purpose. This can be done by modifying the above catch block as shown below.
BEGIN CATCH
Insert into ErrorLog
SELECT
GetDate(),
CONVERT(sysname, CURRENT_USER),
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
The error is now recorded to Errorlog table as shown in snapshot below.
Difference between @@Error and “Try and Catch”
| @@Error | Try and Catch |
| It only reports error | It catches error |
| It’s value is set after every statement | An error (not all errors) in sql statement transfer controls to catch block. |
| It’s difficult to maintain an error log table | An error log table can be easily maintained for future analysis. |
Try and Catch is the preferred way to catch and record errors and should be used as a best practise.
Summary
In this class I talked about 2 different ways to handle errors;@@Error and Try and Catch block.
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
