APPX Software Library

Chapter 5: SQL Statements

5.1 CALL

Description

Invokes a stored procedure.

Syntax

[ ? = ] CALL proc_name([parameter][,...]);

Arguments

[ ? = ]

A parameter marker for the return value of the procedure. Programs must determine if the procedure returns a value and use the parameter marker as a placeholder for a variable that will receive the return value.

CALL proc_name

The name of the procedure to invoke.

parameter

Literal or variable values to pass to the procedure.

Example

The following examples shows invocation of the SQLPROC built-in procedure from interactive SQL. It invokes SQLPROC to retrieve information about another built-in procedure, SQLTABLES:

CALL SQLPROC('',0,'systpe',6,'sqltables',9); -- specific procedure
PROCEDURE_QUALIFIER  PROCEDURE_OWNER  PROCEDURE_NAME  NUM_INPU NUM_OUTP NUM_RESU REMARKS                     PROCEDUR
-------------------  ---------------  --------------  -------- -------- -------- -------                     --------
                     systpe               sqltables          6        0        1 Returns info about a table         1

1 record returned

Authorization

  • Users must have the DBA or EXECUTE privilege to invoke a stored procedure.
  • Users invoking a stored procedure do not need privileges to database objects accessed by the procedure. When a user executes a stored procedure, SQL checks the privileges of the procedure owner, not the procedure user, on any objects that the procedure accesses. This enables a user to execute a procedure successfully even when that user does not have the privileges to directly access objects used by the procedure.
SQL Compliance

ODBC Extended SQL grammar, when enclosed in ODBC escape clause

Environment

Embedded SQL, interactive SQL, ODBC applications

CREATE PROCEDURE, DROP PROCEDURE