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™ 2000 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.