Error Handling – RAISERROR

In my earlier essay, I discussed SQL Server’s error handling in great detail. This is a follow-up post; it discusses RAISERROR, which produces an error message and initiates the session’s error processing. The user-defined message stored in the sys.messages catalog view may be referred to by RAISERROR, or a dynamic message may be created.

Syntax:
RAISERROR ( { msg_id | msg_str | @local_variable }
{ , severity, state }
[ , argument [ , …n ] ] )
[ WITH option [ , …n ] ]

Example1:

— Preparing the dataset

DROP TABLE IF EXISTS t

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

— Creating the procedure
CREATE PROCEDURE uspUpdateValue

AS

BEGIN TRY

    DECLARE @ErrorMessage VARCHAR(100)

    SET @ErrorMessage = ‘An error while updating Table t’

    UPDATE t SET value = ‘abcd’

END TRY

BEGIN CATCH

RAISERROR(@ErrorMessage, 1, 1)

END CATCH;

GO

— Execution

EXEC uspUpdateValue

The “value” column has an integer data type, thus when you run the stored procedure in order to update the value “abcd,” you’ll get the error message “An error while updating Table t.”

This usually returns an error message anyway. The intension of this example is to let you know how to use RAISERROR.

Custom messages aid in identifying the precise location of the problem. As a result, it saves time and effort.

Hope you find this article helpful.