Control Statements › CALL

CALL

The CALL control statement invokes SQL-invoked procedures. The syntax and semantics are a subset of the CALL DML statement.

Authorization

See Authorization.

Syntax

►►──── CALL ───── procedure-reference ─────────────────────────────────────►◄

Parameter

procedure-reference

Identifies the procedure that is invoked, the input values that pass to the procedure and optionally the local variables and routine parameters for passing and returning values of input/output parameters.

Usage

See Usage.

Example

The function GET_NAME invokes the SQL procedure GET_FIRST_LAST to retrieve the first and last names of the employee's empid that is specified as the input parameter. The function then returns the combined and trimmed first and last names as one string.

set options command delimiter '++';
create procedure GET_FIRST_LAST
  ( P_EMPID NUMERIC(4)
  , P_FNAME char(20)
  , P_LNAME  char(30))
   EXTERNAL NAME DEMOGTFL LANGUAGE SQL
  /*
  ** Get first and last name of employee
  */
  Select EMP_FNAME, EMP_LNAME into P_FNAME, P_LNAME From DEMOEMPL.EMPLOYEE
    where EMP_ID = P_EMPID
++
commit++

create function GET_NAME
  (P_ID NUMERIC(4))  RETURNS varchar(40)
   EXTERNAL NAME DEMOGETN LANGUAGE SQL
begin not atomic
 /*
 ** Get name of employee
 */
 declare FNAME char(20);
 declare LNAME char(20);
 call GET_FIRST_LAST(P_ID, FNAME, LNAME);
 return trim(FNAME)|| ' ' || trim(LNAME);
end++
set options command delimiter default++
commit;

select GET_NAME(5008) from SYSCA.SINGLETON_NULL;
*+
*+ USER_FUNC
*+ -----------------------
*+ Timothy Fordman
*+
*+ 1 row processed