Temporary Table Parameter

The temporary table must have been declared prior to procedure execution. However, it does not have to be populated (because the procedure itself can place rows into the table). Upon invocation of the procedure, Ingres binds the executing procedure unambiguously to the global temporary table instance of the invoking session. This permits any number of users, each with their own temporary table instance, to execute the procedure concurrently.

Example:

execute procedure gttproc (parm1 = session.mygtt1);

This statement invokes the procedure gttproc, passing the global temporary table session.mygtt1 as its parameter. (The name used for the actual parameter is inconsequential.)

Limitations of Temporary Table Parameter

When a global temporary table is passed as a procedure parameter, it must be the only parameter in both the calling and called parameter list (that is, in both the EXECUTE PROCEDURE and CREATE PROCEDURE statements).

The columns of the temporary table declaration and the elements in the set of parameter definition must exactly match in degree (number), name, type, and nullability. A check is performed during the execute procedure compile to assure that this constraint is met.

Temporary table parameters cannot be used in nested procedure calls. Global temporary tables cannot be declared within a procedure; hence no locally created temporary table can be passed in an EXECUTE PROCEDURE statement nested in another procedure. Likewise, a set of parameter cannot be specified in a nested EXECUTE PROCEDURE statement.

Execute Procedure Loops

Use an execute procedure loop to retrieve and process rows returned by a row producing procedure using the RESULT ROW clause. The RESULT ROW clause identifies the host variables into which the values produced by the procedure return row statement are loaded. The entries in the RESULT ROW clause must match in both number and type the corresponding entries in the RESULT ROW declaration of the procedure. For more information, see Create Procedure.

The begin-end statements delimit the statements in the execute procedure loop. The code is executed once for each row as it is returned from the row producing procedure. Statements cannot be placed between the EXECUTE PROCEDURE statement and the BEGIN statement.

During the execution of the execute procedure loop, no other statements that access the database can be issued - this causes a runtime error. However, if your program is connected to multiple database sessions, you can issue queries from within the execute procedure loop by switching to another session. To return to the outer execute procedure loop, switch back to the session in which the EXECUTE PROCEDURE statement was issued. To avoid preprocessor errors, the nested queries cannot be within the syntactic scope of the loop but must be referenced by a subroutine call or some form of a GOTO statement.

There are two ways to terminate an execute procedure loop: run it to completion or issue the ENDEXECUTE statement. A host language GOTO statement cannot be used to exit or return to the execute procedure loop.

To terminate an execute procedure loop before all rows are retrieved the application must issue the ENDEXECUTE statement. This statement must be syntactically within the begin-end block that delimits the ENDEXECUTE procedure loop. For more information, see Execute Procedure.

The following example retrieves a set of rows from a row producing procedure:

exec sql execute procedure deptsal_proc (deptid = :deptno)
result row (:deptname, :avgsal, :empcount);
exec sql begin;
 browse data;
if error condition then
exec sql endexecute;
 end if;
exec sql end;"

Permissions

To execute a procedure that you do not own, you must specify the schema parameter, and must have execute privilege for the procedure.

Locking

The locks taken by the Execute Procedure statement depend on the statements that are executed inside the procedure. All locks are taken immediately when the procedure is executed.

Related Statements

Create Procedure

Drop Procedure

Grant (privilege)

Examples: Execute Procedure

The following examples assume the following Create Procedure statement has been successfully executed:

exec sql create procedure p
(i integer not null,
d date,
c varchar(100)) as ...

1. The following example uses a host language variable, a null constant, and an empty string.

   exec sql execute procedure p
   (i=:ivar, d=null, c='')
   into :retstat;

2. The following example assumes the c parameter is null and uses a null indicator for the d parameter.

   exec sql execute procedure p
   (i=:ivar, d=:dvar:ind)
   into :retstat;

3. The following example demonstrates the use of the whenever statement for intercepting errors and messages from a database procedure.

   exec sql whenever sqlerror goto err_exit;
   exec sql whenever sqlmessage call sqlprint;
   
   exec sql execute procedure p into :retstat;
   ...

   err_exit:
   exec sql inquire_sql (:errbug = errortext);

4. The following example demonstrates a dynamically-executed execute procedure statement. The example creates and executes the dynamic equivalent of the following statement.

   exec sql execute procedure enter_person
   (age = :i4_var, comment = :c100_var:indicator);

   Dynamic version:

   exec sql include sqlda;
   allocate an SQLDA with 10 elements;
   sqlda.sqln = 10;
   sqlda.sqld = 2;
   
   /* 20-byte character for procedure name */
   proc_name = 'enter_person';
   
   /* 4-byte integer to put into parameter "age" */
   sqlda.sqlvar(1).sqltype = int;
   sqlda.sqlvar(1).sqllen = 4;
   sqlda.sqlvar(1).sqldata = address(i4_var)
   sqlda.sqlvar(1).sqlind = null;
   sqlda.sqlvar(1).sqlname ='age';
   
   /* 100-byte nullable character to put into the
   ** parameter "comment"
   */
   sqlda.sqlvar(2).sqltype = char;
   sqlda.sqlvar(2).sqllen = 100;
   sqlda.sqlvar(2).sqldata = address(c100_var);
   sqlda.sqlvar(2).sqlind = address(indicator);
   sqlda.sqlvar(2).sqlname = 'comment';
   
   exec sql execute procedure :proc_name
   using descriptor sqlda;

5. Call a database procedure, passing parameters by reference. This enables the procedure to return the number of employees that received bonuses and the total amount of bonuses conferred.

   exec sql execute procedure grant_bonuses
   (ecount = byref(:number_processed),
   btotal = byref (:bonus_total));