raiserror

Description

Prints a user-defined error message on the user’s screen and sets a system flag to record that an error condition has occurred.

Syntax

raiserror error_number 
	[{format_string | @local_variable}] [, arg_list]
	[with errordata restricted_select_list]

Parameters

error_number

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.

format_string

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:

This figure shows a text message in Japanese.

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.

@local_variable

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.

arg_list

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.

with errordata

supplies extended error data for Client-Library™ programs.

restricted_select_list

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.

For more information, see the Transact-SQL User’s Guide.

Examples

Example 1

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

Example 2

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

Example 3

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

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

raiserror permission defaults to all users. No permission is required to use it.

See also

Commands declare, print

System procedures sp_addmessage, sp_getmessage