Executes a stored procedure. EXECUTE is available with the DB2, ODBC, Oracle, and Sybase versions of Production Reporting. (For DB2, Production Reporting does not support overloaded stored procedures.)
EXECUTE [-XC][ON-ERROR=procedure[(arg1[,argi]...)]] [DO=procedure[(arg1[,argi]...)]] {[@#status_var=]stored_procedure_name}| {[@$return_var=]stored_procedure_name} [[@param=]{any_col|_var|_lit}[OUTPUT][,...]] [INTO any_coldata_type[(length_int_lit)] [,...]][WITH RECOMPILE]
The syntax of EXECUTE roughly follows that of the Sybase Transact-SQL EXECUTE command, with the exception of optional arguments and the INTO argument.
(Sybase only) Specifies that EXECUTE shares the same connection as the DO= procedure it can invoke. This argument is required to share Sybase temporary tables.
Production Reporting procedure to execute if an error occurs. If ON‑ERROR is omitted and an error occurs, Production Reporting halts with an error message. For severe errors (for example, passing too few arguments) Production Reporting halts, even if an error procedure is specified.
You can specify arguments to pass to the ON-ERROR procedure. Arguments can be any variable, column, or literal.
Production Reporting procedure to execute for each row selected in the query. Processing continues until all rows are retrieved.
You can specify arguments to pass to the procedure. Arguments can be any variable, column, or literal.
The procedure’s status in the specified numeric variable. The status is returned only after selected rows are retrieved.
(Oracle only) The called stored function's return value into the specified variable. Oracle stored functions can return any column data type. No procedure status is returned for Oracle stored procedures.
Stored procedure or function to execute.
For Oracle installations only, you can add schema and package information to the stored procedure name as follows: [[schema.][package.]].stored_procedure_name.
@param Parameter passed to the stored procedure. Parameters can be passed with or without names. If used without names, they must be listed in the same sequence as defined in the stored procedure.
Value passed to the stored procedure. It can be a string, numeric, or date variable, a previously selected column, a numeric literal, or a string literal.
Indicates that the parameter receives a value from the stored procedure. The parameter must be a string, numeric, or date Production Reporting variable. Output parameters receive their values only after rows selected have been retrieved. If you specify multiple output parameters, they must be in the same sequence as defined in the stored procedure.
Where to store rows retrieved from the stored procedure's SELECT statement. The INTO argument contains the names of the columns with data types and lengths (if needed). You must specify the columns in the same sequence and match the data type used in the stored procedure's SELECT statement.
Table 40 lists the valid data types for each database.
If the stored procedure contains more than one result set, only the first query is described with the INTO argument. Rows from subsequent queries are ignored.
WITH RECOMPILE
(Sybase and ODBC only) Causes the query to recompile each time it executes rather than using the plan stored with the procedure. Normally, this is not required or recommended.
If the stored procedure specified in stored_procedure_name contains a SELECT query, EXECUTE must specify an INTO argument in order to process the values from the query. If no INTO argument is specified, then the values from the query are ignored.
EXECUTE retrieves just the first row when the following instances are true:
This is useful for queries returning a single row.
Oracle stored functions can return any column data type. ODBC, Sybase and DB2 can only return a numeric status.
Oracle and DB2 can return multiple Result Sets of data; however, Production Reporting only processes the first Result Set returned from a stored procedure or function. After processing the first Result Set, all other Result Sets are ignored.
When Oracle or DB2 encounters an INTO clause, an implied Result-Set handle is created. The implied Result-Set handle processes an open cursor returned from a stored procedure or function. The procedure or function is “described” to ensure that the stored object returns a handle to a result set. The data returned from the “describe” is then used to validate the data types declared for each column contained in the INTO clause of the EXECUTE command.
The following example invokes the stored procedure get_total with two parameters: a string literal and a string variable. The result from the stored procedure is stored in the variable #total.
execute get_total 'S. Q. Reporter' $State #Total Output
The following example invokes the stored procedure get_products with two parameters. The stored procedure selects data into five column variables. The Production Reporting procedure print_products is called for each row retrieved. The return status from the stored procedure is placed in the variable #proc_return_status.
execute do=print_products @#proc_return_status= get_products @prodcode=&code,@max=#maximum INTO &prod_code int, &description char(45), &discount float, &restock char, &expire_date datetime begin-procedure print_products print &prod_code(+1,1) print &description(+5,45) print &discount(+5) edit 99.99 print &restock(+5) match Y 0 5 Yes N 0 5 No print &expire_date(+5,) edit 'Month dd, yyyy' end-procedure