Microsoft
SQL Server DECLARE and EXECUTE
PowerBuilder requires a declarative statement to identify the
database stored procedure that is being used and a logical name that can
be referenced in subsequent SQL statements.
The general syntax for declaring a procedure is:
|
1 2 3 4 5 |
DECLARE logical_procedure_name PROCEDURE FOR SQL_Server_procedure_name @Param1 = value1, @Param2 = value2, @Param3 = value3 OUTPUT, {USING transaction_object} ; |
where logical_procedure_name can be any valid PowerScript data
identifier and SQL_Server_procedure_name is the name of the stored
procedure in the database.
The parameter references can take the form of any valid parameter
string that SQL Server accepts. PowerBuilder does not inspect the
parameter list format except for purposes of variable substitution. You
must use the reserved word OUTPUT to indicate an output parameter. 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 proc1 is defined as:
|
1 2 |
CREATE PROCEDURE proc1 AS SELECT emp_name FROM employee |
To declare that procedure for processing within PowerBuilder,
enter:
|
1 |
DECLARE emp_proc PROCEDURE FOR proc1; |
Note that this declaration is a nonexecutable statement, just like a
cursor declaration. 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:
|
1 |
EXECUTE emp_proc; |
Example 2
To declare a procedure with input and output parameters,
enter:
|
1 2 3 4 |
DECLARE sp_duration PROCEDURE FOR pr_date_diff_prd_ken @var_date_1 = :ad_start, @var_date_2 = :ad_end, @rtn_diff_prd = :ls_duration OUTPUT; |