Prints a user-defined message on the user’s screen.
print
{format_string | @local_variable |
@@global_variable}
[, arg_list]
can be either a variable or a string of characters. The maximum length of format_string is 1023 bytes.
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.
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.
must be of type char, nchar, varchar, or nvarchar, and must be declared within the batch or procedure in which it is used.
must be of type char or varchar, or be automatically convertible to these types, such as @@version. Currently, @@version is the only character-type global variable.
may be a series of either variables or constants separated by commas. arg_list is optional unless a format string containing placeholders of the form “%nn !” is provided. In that case, the arg_list must have at least as many arguments as the highest numbered placeholder. An argument can be any datatype except text or image; it is converted to a character datatype before being included in the final message.
Prints “Berkeley author” if any authors in the authors table live in the 94705 ZIP code:
if exists (select postalcode from authors where postalcode = '94705') print "Berkeley author"
Declares a variable, assigns a value to the variable, and prints the value:
declare @msg char(50) select @msg = "What's up, doc?" print @msg
What's up, doc?
Demonstrates the use of variables and placeholders in messages:
declare @tabname varchar(30) select @tabname = "titles" declare @username varchar(30) select @username = "ezekiel" print "The table '%1!' is not owned by the user '%2!'.", @tabname, @username
The table 'titles' is not owned by the user 'ezekiel.'
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 also 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 print is executed.
The arg_list must include an argument for each placeholder in the format_string, or the transaction is aborted. You can use more arguments than placeholders.
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 character
string. If you do not want zero-length strings in the output, use
the isnull function. For example, if @arg is
null, the following statement prints I think
we have nothing here.:
declare @arg varchar(30) select @arg = isnull(col1, "nothing") from table_a where ...
print "I think we have %1! here", @arg
User-defined messages can be added to the system table sysusermessages for use by any application. Use sp_addmessage to add messages to sysusermessages; use sp_getmessage to retrieve messages for use by print and raiserror.
Use raiserror instead of print to print a user-defined error message and have the error number stored in @@error.
SQL92 – Compliance level: Transact-SQL extension.
print permission defaults to all users. No permission is required to use it.
System procedures sp_addmessage, sp_getmessage
