Prints a user-defined error message on the user’s screen and sets a system flag to record that an error condition has occurred.
raiserror error_number
[{format_string | @local_variable}] [, arg_list]
[with errordata restricted_select_list]
is a local variable or an integer with a value greater than 17,000. If the error_number is between 17,000 and 19,999, and format_string is missing or empty (""), Adaptive Server retrieves error message text from the sysmessages table in the master database. These error messages are used chiefly by system procedures.
If error_number is 20,000 or greater and format_string is missing or empty, raiserror retrieves the message text from the sysusermessages table in the database from which the query or stored procedure originates. Adaptive Server attempts to retrieve messages from either sysmessages or sysusermessages in the language defined by the current setting of @@langid.
is a string of characters with a maximum length of 1024 bytes. Optionally, you can declare format_string in a local variable and use that variable with raiserror (see @local_variable).
raiserror recognizes placeholders in the character string that is to be printed out. Format strings can contain up to 20 unique placeholders in any order. These placeholders are replaced with the formatted contents of any arguments that follow format_string, when the text of the message is sent to the client.
To allow reordering of the arguments, when format strings are translated to a language with a different grammatical structure, the placeholders are numbered. A placeholder for an argument appears in this format: “%nn!”—a percent sign (%), followed by an integer from 1 to 20, followed by an exclamation point (!). The integer represents the argument number in the string in the argument list. “%1!” is the first argument in the original version, “%2!” is the second argument, and so on.
Indicating the position of the argument in this way makes it possible to translate correctly, even when the order in which the arguments appear in the target language is different from their order in the source language.
For example, assume the following is an English message:
%1! is not allowed in %2!.
The German version of this message is:
%1! ist in %2! nicht zulassig.
The Japanese version of this message is:
In this example, “%1!” represents the same argument in all three languages, as does “%2!”. This example shows the reordering of the arguments that is sometimes necessary in the translated form.
is a local variable containing the format_string value. It must be of type char or varchar and must be declared within the batch or procedure in which it is used.
is a series of variables or constants separated by commas. arg_list is optional unless a format string containing placeholders of the form “%nn !” is provided. An argument can be any datatype except text or image; it is converted to the char datatype before being included in the final string.
If an argument evaluates to NULL, Adaptive Server converts it to a zero-length char string.
supplies extended error data for Client-Library™ programs.
consists of one or more of the following items:
“*”, representing all columns in create table order.
A list of column names in the order you want to see them. When selecting an existing IDENTITY column, you can substitute the syb_identity keyword, qualified by the table name, where necessary, for the actual column name.
A specification to add a new IDENTITY column to the result table:
column_name = identity (precision)
A replacement for the default column heading (the column name), in the following forms:
column_heading = column_name
column_name column_heading
column_name as column_heading
The column heading may be enclosed in quotation marks for any of these forms. The heading must be enclosed in quotation marks if it is not a valid identifier (that is, if it is a reserved word, if it begins with a special character, or if it contains spaces or punctuation marks).
An expression (a column name, constant, function, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators, or a subquery).
A built-in function or an aggregate.
Any combination of the items listed above.
The restricted_select_list can also perform variable assignment, in the form:
@variable = expression [, @variable = expression ...]
Restrictions to restricted_select_list are:
You cannot combine variable assignment with any of the other restricted_select_list options.
You cannot use from, where, or other select clauses in restricted_select_list.
You cannot use “*” to represent all columns in restricted_select_list.
See the Transact-SQL User’s Guide.
This stored procedure example returns an error if it does not find the table supplied with the @tabname parameter:
create procedure showtable_sp @tabname varchar (18) as if not exists (select name from sysobjects where name = @tabname) begin raiserror 99999 "Table %1! not found.", @tabname end else begin select sysobjects.name, type, crdate, indid from sysindexes, sysobjects where sysobjects.name = @tabname and sysobjects.id = sysindexes.id end
This example adds a message to sysusermessages, then tests the message with raiserror, providing the substitution arguments:
sp_addmessage 25001, "There is already a remote user named '%1!' for remote server '%2!'." raiserror 25001, jane, myserver
This example uses the with errordata option to return the extended error data column and server to a client application, to indicate which column was involved and which server was used:
raiserror 20100 "Login must be at least 5 characters long" with errordata "column" = "login", "server" = @@servername
User-defined messages can be generated ad hoc, as in Example 1 and Example 3, or they can be added to the system table sysusermessages for use by any application, as shown in Example 2. Use sp_addmessage to add messages to sysusermessages; use sp_getmessage to retrieve messages for use by print and raiserror.
Error numbers for user-defined error messages must be greater than 20,000. The maximum value is 2,147,483,647 (231 -1).
The severity level of all user-defined error messages is 16. This level indicates that the user has made a nonfatal error.
The maximum output string length of format_string plus all arguments after substitution is 1024 bytes.
If you use placeholders in a format string, keep this in mind: for each placeholder n in the string, the placeholders 1 through n-1 must exist in the same string, although they do not have to be in numerical order. For example, you cannot have placeholders 1 and 3 in a format string without having placeholder 2 in the same string. If you omit a number in a format string, an error message is generated when raiserror is executed.
If there are too few arguments relative to the number of placeholders in format_string, Adaptive Server displays an error message and aborts the currently executing statement, but does not abort any open transactions. However, if this error occurs within a stored procedure, Adaptive Server continues with the next statement at the line that called raiserror, and any open transactions remain open. If the error occurs in a batch of SQL code, Adaptive Server aborts the batch, and any open transactions remain open.
To include a literal percent sign as part of the error message, use two percent signs (‘‘%%’’) in the format_string. If you include a single percent sign (‘‘%’’) in the format_string that is not used as a placeholder, Adaptive Server returns an error message.
If an argument evaluates to NULL, it is converted into a zero-length char string. If you do not want zero-length strings in the output, use the isnull function.
When raiserror is executed, the error number is placed in the global variable @@error, which stores the error number that was most recently generated by the system.
Use raiserror instead of print if you want an error number stored in @@error.
To include an arg_list with raiserror, put a comma after error_number or format_string before the first argument. To include extended error data, separate the first extended_value from error_number, format_string, or arg_list using a space (not a comma).
ANSI SQL – Compliance level: Transact-SQL extension.
raiserror permission defaults to all users. No permission is required to use it.
System procedures sp_addmessage, sp_getmessage
