SET Host Variable

The SET host-variable assignment statement assigns values, either of expressions or NULL values, to host variables.

Invocation

SET Host Variable is an executable statement that can only be embedded in a host program. The statement cannot be dynamically prepared.

Authorization

The privileges that are held by the current authorization ID must include those required to execute any of the expressions.

Syntax

SET host-variable = 
{   {CURRENT SERVER | CURRENT PACKAGESET}
    | host-variable = {expression | NULL}
    | (host-variable[,...]) = ({expression | NULL} [,...] | VALUES {{expression | NULL} | ({expression | NULL}[,...])}})}

Parameters

host-variable
Identifies one or more host variables or transition variables that are used to receive the corresponding expression or NULL value on the right side of the statement.

If the SET Assignment statement is used in the triggered action of a CREATE TRIGGER statement, each host-variable must identify a transition variable. If the statement is used in any other context, each host-variable must identify a host variable.

The value to be assigned to each host-variable can be specified immediately following the item reference, for example, host-variable = expression, host-variable=expression. Or, sets of parentheses can be used to specify all the host-variables and then all the values, for example, (host-variable, host-variable) = (expression, expression).

Each host variable must be defined in the program as described under the rules for declaring host variables. A parameter marker must not be specified in place of host-variable.

expression
Specifies the value to be assigned to the corresponding host-variable.

When the host-variables are enclosed within parentheses, for example, (host-variable, host-variable, ...) = (expression, expression, ...), the first value is assigned to the first host variable in the list, the second value to the second host variable in the list, and so on.

NULL
Specifies the null value and can only be specified for host variables that have an associated indicator variable.
VALUES
Specifies the value to be assigned to the corresponding host variable. When more than one value is specified, the values must be enclosed in parentheses. Each value can be an expression or NULL, as described above. The following syntax is equivalent: 
(host-variable, host-variable) = (VALUES(expression, NULL))
(host-variable, host-variable) = (expression, NULL)

Special Registers

Local special registers can be referenced only in a VALUES host-variable statement that results in the assignment of a single host variable and not those that result in setting more than one value.

Special registers that can be defined to a host variable include:

CURRENT DATE CURRENT PACKAGESET
CURRENT TIME CURRENT SERVER
CURRENT TIMESTAMP CURRENT SQLID
CURRENT TIMEZONE USER

For example: SET :your-var-name = CURRENT SERVER

The value of CURRENT SQLID can be changed by issuing a SET CURRENT SQLID command. The CURRENT PACKAGESET special register (and the SET CURRENT PACKAGESET command) currently have limited functional support by the XDB Server.

For information about each of these special registers, see Special Registers.

Parent topic: SQL Statements