Using RAISERROR

RAISERROR is a more powerful statement than PRINT for returning messages back to applications. RAISERROR can return messages in either of these forms:

A user-defined error message that has been added to master.dbo.sysmessages using the sp_addmessage system stored procedure.
A message string specified in the RAISERROR statement.

RAISERROR also has these extensions to the capabilities of PRINT:

RAISERROR can assign a specific error number, severity, and state.
RAISERROR can request that the error be logged in the Microsoft® SQL Server™ error log and the Microsoft Windows NT® application log.
The message string can contain substitution variables and arguments, much like the C language printf function.

When RAISERROR is used with the msg_id of a user-defined message in sysmessages, msg_id is returned as the SQL Server error number, or native error code. When RAISERROR is used with a msg_str instead of a msg_id, the SQL Server error number and native error number returned is 50000.

When you use RAISERROR to return a user-defined error message, use a different state number in each RAISERROR that references that error. This can aid in diagnosing the errors when they are raised.

Use RAISERROR to help in troubleshooting Transact-SQL code, to check the values of data, or to return messages that contain variable text.

This example substitutes the values from the DB_ID and DB_NAME functions in a message sent back to the application:

DECLARE @DBID INT

SET @DBID = DB_ID()

DECLARE @DBNAME NVARCHAR(128)

SET @DBNAME = DB_NAME()

RAISERROR

('The current database ID is:%d, the database name is: %s.',

16, 1, @DBID, @DBNAME)

This example accomplishes the same process using a user-defined message:

sp_addmessage 50005, 16,

'The current database ID is:%d, the database name is: %s.'

GO

DECLARE @DBID INT

SET @DBID = DB_ID()

DECLARE @DBNAME NVARCHAR(128)

SET @DBNAME = DB_NAME()

RAISERROR (50005, 16, 1, @DBID, @DBNAME)

GO

This second RAISERROR example shows that substitution parameters can be specified in a user-defined error and filled with substitution arguments at the time the RAISERROR statement executes.