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:

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
    DECLARE @ErrorMessage VARCHAR(100),
    SET @ErrorMessage = 'An error while updating Table t'
    UPDATE t SET value = @ipv_value
END TRY
BEGIN CATCH
	RAISERROR(@ErrorMessage, @Severity, @State)
END CATCH;
GO

-- Execution
EXEC uspUpdateValue 'abcd'

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

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.