The Procedure Header
Go Up to Creating Procedures
Everything before AS in the CREATE PROCEDURE statement forms the procedure header. The header contains:
- The name of the stored procedure, which must be unique among procedure and table names in the database.
- An optional list of input parameters and their data types. The procedure receives the values of the input parameters from the calling program.
- Optionally, the
RETURNSkeyword followed by a list of output parameters and their data types. The procedure returns the values of the output parameters to the calling program.
Declaring Input Parameters
Use input parameters to pass values from an application to a procedure. Any input parameters are given in a comma-delimited list enclosed in parentheses immediately after the procedure name, as follows:
CREATE PROCEDURE name
(var data_type [, var data_type …])
. . .
Each input parameter declaration has two parts: a name and a data type. The name of the parameter must be unique within the procedure, and the data type can be any standard SQL data type except arrays of data types. The name of an input parameter need not match the name of any host parameter in the calling program.
No more than 1,400 input parameters can be passed to a stored procedure.
Declaring Output Parameters
Use output parameters to return values from a procedure to an application. The RETURNS clause in the procedure header specifies a list of output parameters. The syntax of the RETURNS clause is:
...
[RETURNS (var data_type [, var data_type …])]
AS
...
Each output parameter declaration has two parts: a name and a data type. The name of the parameter must be unique within the procedure, and the data type can be any standard SQL data type except arrays.