Error Handling – Introduction

In SQL Server, error handling (also known as exception handling) offers us control over the Transact-SQL code and aids in diagnosing the precise point where it failed. The only function of this method is to report the cause of failure. Due to this, we will be able to figure out what went wrong and rectify it.

The majority of SQL Server error messages are self-explanatory and simple to comprehend, but a user-defined message is far more readable, meaningful, and understandable to users, developers, etc., and it also saves us a lot of time.

And, this is possible using TRY and CATCH blocks.

A TRY block can be used to contain a collection of Transact-SQL queries. Normally, if there is a problem with the TRY block, control is passed to another group of statements that are enclosed in a CATCH block. To put it another way, the TRY block is where the code encounters a problem or error, and the CATCH block is where the following system-defined functions are used to return the specifics of the error.

ERROR_NUMBER – This function returns the error number of the error that caused the CATCH block of a TRY…CATCH construct to execute.
ERROR_STATE – Returns the state number of the error that caused the CATCH block of a TRY…CATCH construct to be run.
ERROR_SEVERITY – This function returns the severity value of the error where an error occurs, if that error caused the CATCH block of a TRY…CATCH construct to execute.
ERROR_LINE – This function returns the line number of occurrence of an error that caused the CATCH block of a TRY…CATCH construct to execute.
ERROR_PROCEDURE – This function returns the name of the stored procedure or trigger where an error occurs, if that error caused the CATCH block of a TRY…CATCH construct to execute.
ERROR_MESSAGE – This function returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to execute.

Here’s an example (Curtesy: Microsoft Documentation).

BEGIN TRY  
    -- Generate a divide-by-zero error.  
    SELECT 1/0;  
END TRY  
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;  
GO

Here is the output

ErrorNumber	ErrorSeverity	ErrorState	ErrorProcedure	ErrorLine	ErrorMessage
8134	16	1	NULL	4	Divide by zero error encountered.

Another example:

CREATE TABLE t
( tid INT NOT NULL
, name VARCHAR(20) NOT NULL
, value INT NOT NULL
, PRIMARY KEY (tid)
) ;

INSERT INTO t VALUES (1, 'John', 20) ;
INSERT INTO t VALUES (2, 'John',  7) ;
INSERT INTO t VALUES (3, 'John',  7) ;
INSERT INTO t VALUES (4, 'John',  7) ;
INSERT INTO t VALUES (5, 'John',  4) ;

INSERT INTO t VALUES (11, 'Bill', 27) ;
INSERT INTO t VALUES (12, 'Bill', 27) ;
INSERT INTO t VALUES (13, 'Bill', 27) ;
INSERT INTO t VALUES (14, 'Bill', 27) ;
INSERT INTO t VALUES (15, 'Bill',  4) ;

INSERT INTO t VALUES (21, 'Mary', 10) ;
INSERT INTO t VALUES (22, 'Mary', 10) ;
INSERT INTO t VALUES (23, 'Mary',  7) ;
INSERT INTO t VALUES (24, 'Mary',  6) ;
INSERT INTO t VALUES (25, 'Mary',  4) ;
GO


CREATE PROCEDURE uspUpdateValue @ipv_value INT
AS
BEGIN TRY
    UPDATE t SET value = @ipv_value
END TRY
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;
GO

-- Execution
EXEC uspUpdateValue 'abcd';

As “value” is an INTEGER column, attempting to update it with “abcd” will result in an error.

The aforementioned examples all involve “system” errors. Please click the following link to view “custom” errors.

RAISERROR

Hope you find this article helpful.