Returns descriptive information for the input and return parameters for stored procedures in the current environment.
sp_sproc_columns sp_name [, sp_owner] [, sp_qualifier] [, column_name]
is the name of the stored procedure. Use the wildcard character to request information about more than one stored procedure.
is the owner of the stored procedure. Use the wildcard character to request information about stored procedures owned by more than one user. If you do not specify this parameter, sp_sproc_columns looks first for a procedure owned by the current user and then for a procedure owned by the database owner.
is ignored. Leave blank or set to NULL.
is the set of columns to be included in the result set. Use the wildcard character to request information about more than one column. If you do not supply a column_name parameter, sp_sproc_columns returns information about all columns for the stored procedure.
The access service selects information from the SYSPROCCOLUMNS table. The cspdb2.sql script creates this table during installation of DirectConnect for z/OS Option. However, you need to update the SYSPROCCOLUMNS table manually.
This function corresponds to the ODBC function SQLProcedureColumns.
sp_sproc_columns returns a list of available procedures. Results are ordered by the following columns:
PROCEDURE_OWNER
PROCEDURE_NAME
COLUMN_TYPE
The lengths for varchar columns shown in the result set tables are maximums; the actual lengths depend on the target database.
Table 11-13 shows the result set for sp_sproc_columns.
Column |
Datatype |
Description |
|---|---|---|
PROCEDURE_QUALIFIER |
varchar(128) |
Always NULL |
PROCEDURE_OWNER |
varchar(128) |
Value from the corresponding column of SYSPROCCOLUMNS table |
PROCEDURE_NAME |
varchar(128) NOT NULL |
Name of the stored procedure |
COLUMN_NAME |
varchar(128) NOT NULL |
Name of the input parameter or result set column |
COLUMN_TYPE |
smallint NOT NULL |
Type of data in this procedure column:
|
DATA_TYPE |
smallint NOT NULL |
Integer code for the ODBC SQL datatype equivalent of the target database datatype for this procedure column |
TYPE_NAME |
varchar(128) NOT NULL |
String representing the datatype name in the target database |
PRECISION |
int |
Precision of the procedure column on the target database; NULL if precision is not applicable |
LENGTH |
int |
Length of the column in bytes |
SCALE |
smallint |
Number of digits to the right of the decimal point; NULL if scale is not applicable |
RADIX |
smallint |
Base for numeric types; NULL if radix is not applicable |
NULLABLE |
smallint |
Indicates whether the procedure column accepts NULL values:
|
REMARKS |
varchar(254) |
Description of the procedure column |
