Chapter 11: Accessing Catalog Information with CSPs

sp_sproc_columns

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]

sp_name: is the name of the stored procedure. Use the wildcard character to request information about more than one stored procedure.

sp_owner: 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.

column_name: 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. 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:

The lengths for varchar columns shown in the result set tables are maximums; the actual lengths depend on the target database.

The following table shows the result set for sp_sproc_columns.

**Table 11-13: 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: 1 SQL_PARAM_INPUT – the procedure column is an input parameter 3 SQL_RESULT_COL – the procedure column is a result set 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: 0 – the column does not accept NULL 1 – the column accepts NULL 2 – it is not known if the column accepts NULL values
REMARKS	varchar(254)	Description of the procedure column