Handling Errors in Stored Procedures
RAISERROR ({msg_id |msg_str }{,severity ,state }
[ ,argument [ ,,...n ] ] ))
[ WITH option [ ,,...n ] ]
A description of the components of the statement follows.
msg_id
The ID for an error message, which is stored in the error column in sysmessages. The domain of the error column for custom messages are values greater than 50,000.
msg_str
A custom message that is not contained in sysmessages. The maximum length of the message is 400 characters. Variable substitution can be used to create a more meaningful message.
severity
The severity level associated with the error. The valid values are 0–25. Severity levels 0–18 can be used by any user, but 19–25 are only available to members of the fixed-server role sysadmin. When levels 19–25 are used, the WITH LOG option is required.
state
A value that indicates the invocation state of the error. The valid values are 0–127. This value is not used by SQL Server.
Argument, . . .
One or more variables that are used to customize the message. For example, you could pass the current process ID (@@SPID) so it could be displayed in the message.
WITH option, . . .
The three values that can be used with this optional argument are described here:
LOG - Forces the error to logged in the SQL Server error log and the NT application log.
NOWAIT - Sends the message immediately to the client.
SETERROR - Sets @@ERROR to the unique ID for the message or 50,000.
The number of options available for the statement make it seem complicated, but it is actually easy to use. The following shows how to create an ad hoc message with a severity of 10 and a state of 1.
RAISERROR ('An error occured updating the NonFatal table',10,1)
NOWAIT - Sends the message immediately to the client.
SETERROR - Sets @@ERROR to the unique ID for the message or 50,000.
The number of options available for the statement make it seem complicated, but it is actually easy to use. The following shows how to create an ad hoc message with a severity of 10 and a state of 1.
RAISERROR ('An error occured updating the NonFatal table',10,1)
--Results--
An error occured updating the NonFatal table
The statement does not have to be used in conjunction with any other code, but for our purposes it will be used with the error handling code presented earlier. The following alters the ps_NonFatal_INSERT procedure to use RAISERROR.
USE tempdb
go
ALTER PROCEDURE ps_NonFatal_INSERT
@Column2 int =NULL
AS
DECLARE @ErrorMsgID int
INSERT NonFatal VALUES (@Column2)
SET @ErrorMsgID =@@ERROR
IF @ErrorMsgID <>0
BEGIN
RAISERROR ('An error occured updating the NonFatal table',10,1)
END
The statement does not have to be used in conjunction with any other code, but for our purposes it will be used with the error handling code presented earlier. The following alters the ps_NonFatal_INSERT procedure to use RAISERROR.
USE tempdb
go
ALTER PROCEDURE ps_NonFatal_INSERT
@Column2 int =NULL
AS
DECLARE @ErrorMsgID int
INSERT NonFatal VALUES (@Column2)
SET @ErrorMsgID =@@ERROR
IF @ErrorMsgID <>0
BEGIN
RAISERROR ('An error occured updating the NonFatal table',10,1)
END
When an error-producing call is made to the procedure, the custom message is passed to the client. The following shows the output generated by Query Analyzer
