SQL Server TSQL – The TSQL Classes Stored Procedures: Error Handling

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/764/the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-caching

https://www.sqlservergeeks.com/blogs/AhmadOsama/personal/777/the-tsql-classes-stored-procedures-parameter-sniffing

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.

  1. @@ERROR
  2. 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.

1_The_TSQL_Classes_Stored_Procedures_Error_Handling

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.

2_The_TSQL_Classes_Stored_Procedures_Error_Handling

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.

3_The_TSQL_Classes_Stored_Procedures_Error_Handling

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

   

Leave a Reply

Your email address will not be published.