assignment-statement

Description

label

Specifies the label for assignment-statement. The label name cannot be the same as the routine name, advanced trigger name, or another label within the same scope. For additional information, see References to SQL labels.

Notes

Assignment rules: Assignment statements in SQL routines and triggers must conform to the SQL assignment rules. For example, the data type of the target and source must be compatible. See Assignment and comparison for assignment rules.

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 or double-byte blanks. When a string is assigned to a variable and the string is longer than the length attribute of the variable, the value is truncated and a warning is returned.

If truncation of the whole part of a number occurs on assignment to a numeric variable, the value is truncated and a warning is returned.

Assignments involving SQL parameters for SQL procedures: An IN parameter can appear on the left or right side in an assignment statement. When control returns to the caller, the original value of the IN parameter is retained. An OUT parameter can also appear on the left or right side in an assignment statement. If used without first being assigned a value, the value is undefined. When control returns to the caller, the last value that is assigned to an OUT parameter is returned to the caller. For an INOUT parameter, the first value of the parameter is determined by the caller, and the last value that is assigned to the parameter is returned to the caller.

Multiple assignments: If more than one assignment is included in the same assignment statement, all expressions are evaluated before the assignments are performed. Thus, references to an SQL variable or SQL parameter in an expression always use the value of the SQL variable or SQL parameter prior to any assignment in the assignment statement.

Considerations for SQLSTATE and SQLCODE SQL variables: Assignment to these variables is not prohibited. However, it is not recommended as assignment does not affect the diagnostic area or result in the activation of condition handlers. Furthermore, processing an assignment to these SQL variables causes the specified values for the assignment to be overlayed with the SQL return codes returned from executing the statement that does the assignment.