OLE DB DECLARE and EXECUTE – PB Docs 2021

PowerBuilder requires a declarative statement to identify the
database stored procedure that is being used and to specify a logical name
for the procedure. The logical name is used to reference the procedure in
subsequent SQL statements.

The general syntax for declaring a procedure is:

where logical_procedure_name can be any valid PowerScript identifier
and procedure_name is the name of a stored procedure in the
database.

The parameter references can take the form of any valid parameter
string the database accepts. PowerBuilder inspects the parameter list
format only for variable substitution.

You must use the reserved word OUTPUT or OUT to indicate an output
parameter if you want to get the output parameter value. If the stored
procedure has a return value and you want to get it, use the syntax
“RC=procedure_name”. If the procedure has one or more result sets, only
after all the result set has been retrieved can you get the output
parameter or return value. The USING clause is required only if you are
using a transaction object other than the default transaction object
(SQLCA).

Example 1

Assume a stored procedure named proc1 is defined on the server. To
declare proc1 for processing within PowerBuilder, enter:

The procedure declaration is a nonexecutable statement, just like a
cursor declaration. However, where cursors have an OPEN statement,
procedures have an EXECUTE statement.

When an EXECUTE statement executes, the procedure is invoked. The
EXECUTE refers to the logical procedure name, in this example
emp_proc:

Example 2

This example declares a stored procedure with two input and one
output parameters:

If the stored procedure contains result sets, you must fetch the
result sets first. If the stored procedure has a return value and you want
to obtain it, use the format RC=procedure_name:

See also

OLE DB
EXECUTE