Pb9.0 + sqlserver2000 how to invoke written stored procedures and obtain the parameters of return

CodePudding user response:

Original concept requires a declarative statement to identify the database stored procedure that is being informs the and a logical name that can be referenced in subsequent SQL statements.
The general syntax for declaring a procedure is:

DECLARE logical_procedure_name PROCEDURE FOR
SQL_Server_procedure_name
@ Param1=value1, @ Param2=value2,
@ Param3=value3 OUTPUT,
{the 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. The original concept 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:

CREATE PROCEDURE proc1 AS

The SELECT emp_name FROM employee

To declare that procedure for processing within the original concept, enter:

DECLARE emp_proc PROCEDURE FOR proc1;

Note that this declaration is a nonexecutable statement, just like a cursor declaration. The Where your cursors have an OPEN statement, procedures have an EXECUTE statement.

When an EXECUTE the statement executes, the procedure is invoked, the EXECUTE refers to the logical procedure name:

The EXECUTE emp_proc;

Example 2

To declare a procedure with the input and output parameters, enter:

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;