Progress
SQL-92
Guide and Reference


Passing Values to and from Stored Procedures: Input and Output Parameters

Applications need to pass and receive values from the stored procedures they call. They do this through input and output parameters declared in the procedure specification.

Applications can pass and receive values from stored procedures using input and output parameters declared in the stored procedure specification. When it processes the CREATE PROCEDURE statement, the SQL server declares Java variables of the same name. This means the body of the stored procedure can refer to input and output parameters as if they were Java variables declared in the body of the stored procedure.

Procedure result sets are another way for applications to receive output values from a
stored procedure. Procedure result sets provide output in a row-oriented tabular format.
See the "Returning a Procedure Result Set to Applications: The RESULT Clause and DhSQLResultSet" section for more information.

Parameter declarations include the parameter type (IN, OUT, or INOUT), the parameter name, and SQL data type See the "Implicit Data Type Conversion Between SQL-92 and Java Types" section for details of how SQL data types map to Java data types.

EXAMPLE

Declare input and output parameters in the specification section of a stored procedure, as shown in the following example:

CREATE PROCEDURE order_entry (
     IN  cust_name    CHAR(20),
     IN  item_num     INTEGER,
     IN  quantity     INTEGER,
     OUT status_code  INTEGER,
     INOUT order_num  INTEGER
) 

When the order_entry stored procedures executes, the calling application passes values for the cust_name, item_num, quantity, and order_num input parameters. The body of the procedure refers to them as Java variables. Similarly, Java code in the body of order_entry processes and returns values in the status_code and order_num output parameters.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095