SET Assignment

The SET Assignment statement assigns values to parameters and variables used in SQL routines.

Syntax

►►── SET ──┬─ local-variable ─────┬ = ─┬─ value-expression ─┬─────────────────►◄
           └─ routine-parameter ──┘    └─ NULL ─────────────┘

Parameters

local-variable: Identifies the local variable that is the target of the SET assignment statement. Local-variable must be the name of a local variable defined within the compound statement containing the SET statement.
routine-parameter: Identifies the SQL routine parameter that is the target of the SET assignment statement. Routine-parameter must be the name of a parameter of the routine containing the SET assignment statement.
value-expression: Specifies the value to be assigned to the target of the SET assignment statement.
NULL: Specifies that the null value is to be assigned to the target of the SET assignment statement.

Usage

Valid assignments

The rules for assignment are provided in Comparison, Assignment, Arithmetic, and Concatenation Operations.

Example

The procedure TSET3 creates a combined, edited name from a given first and last name. If the first or last name is null, or if the length of the last name is 0, the null value is returned for the edited name.

set options command delimiter '++';
create procedure SQLROUT.TSET3
  ( P_FNAME   varchar(20)
  , P_LNAME   varchar(20)
  , P _NAME   varchar(41)
  )
    EXTERNAL NAME TSET3 LANGUAGE SQL
 /*
 ** Return an edited name from the given Firstname and Lastname
 */
 if (LENGTH(P_LNAME) <= 0)
   then set P_NAME = null;
   else set P_NAME = trim(P_FNAME)  || ' ' || trim(P_NLNAME) ;
 end if
++
set options command delimiter default++

call SQLROUT.TSET3('James    ', 'Last   ');
*+
*+ P_FNAME               P_LNAME
*+ -------               -------
*+ James                 Last
*+
*+ P_NAME
*+ ------
*+ James Last

call SQLROUT.TSET3('James    ', '');
*+
*+ P_FNAME               P_LNAME
*+ -------               -------
*+ James
*+
*+ P_NAME
*+ ------
*+ <null>