Chapter 1: Commands

print

Description

Prints a user-defined message on the user’s screen.

Syntax

print 
	{format_string | @local_variable | 
	@@global_variable}
		[, arg_list]

Parameters

format_string

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, 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 zulässig.

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: must be of type char, nchar, varchar, or nvarchar, and must be declared within the batch or procedure in which it is used.

@@global_variable: 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.

arg_list: 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.

Examples

Example 1

Prints “Berkeley author” if any authors in the authors table live in the 94705 postal code:

if exists (select postalcode from authors 
where postalcode = '94705') 
print "Berkeley author"

Example 2

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?

Example 3

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.'

Usage

The maximum output string length of format_string plus all arguments after substitution is 1023 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
```
You can add user-defined messages 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.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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