SQL Reference

Assignment Statement

The assignment statement assigns a value to an output parameter, a local variable, or a special register.

Syntax

>>-SET--+-parameter-name----+---=--+-expression-+--------------><
        +-SQL-variable-name-+      '-NULL-------'
        '-special-register--'
 

Description

parameter-name

Identifies the parameter that is the assignment target. The parameter must be specified in parameter-declaration in the CREATE PROCEDURE statement and must be defined as an OUT or INOUT parameter.

SQL-variable-name

Identifies the SQL variable that is the assignment target. SQL variables must be declared before they are used. SQL variables can be defined in a compound statement.

special-register

Identifies the special register that is the assignment target. If the special register accepts a schema name as a value, including the CURRENT FUNCTION PATH or CURRENT SCHEMA special registers, DB2 determines whether the assignment parameter is an SQL variable. If the assignment parameter is an SQL variable, DB2 assigns the value of the SQL variable to the special register. If the assignment parameter is not an SQL variable, DB2 assumes that the assignment parameter is a schema name and assigns that schema name to the special register.

The initial settings of special register values in an SQL procedure are inherited from the caller of the procedure. The assignment of a new setting is valid for the entire SQL procedure where it is set, and will be inherited by any procedure that it subsequently calls. When a procedure returns to its caller, the special registers are restored to the original settings of the caller.

expression or NULL

Specifies the expression or value that is the source for the assignment.

Rules

• Assignment statements in SQL procedures must conform to the SQL assignment rules.
• The data type of the target and source must be compatible.
• When a string is assigned to a fixed-length variable and the length of the string is less than the length attribute of the target, the string is padded on the right with the necessary number of single-byte, double-byte, or UCS-2 blanks.
• When a string is assigned to a variable and the string is longer than the length attribute of the variable, an error is issued.
• A string assigned to a variable is first converted, if necessary, to the codepage of the target.
• If truncation of the whole part of the number occurs on assignment to a numeric variable, an error is raised.

Examples

Increase the SQL variable p_salary by 10 percent.

     SET p_salary = p_salary + (p_salary * .10)

Set SQL variable p_salary to the null value.

     SET p_salary = NULL