Access Service User's Guide DirectConnect for AS/400

Home

Access Service User's Guide DirectConnect for AS/400

Chapter 12 Accessing catalog information with CSPs

sp_sproc_columns

Description

Returns descriptive information for the input and return parameters for stored procedures in the current environment.

Syntax

sp_sproc_columns sp_name [, sp_owner]
 [, sp_qualifier] [, column_name]

Parameters

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.

sp_qualifier

is ignored. Leave blank or set to NULL.

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.

Usage

The access service selects information from the NULLID.SYSPROCS and NULLID.SYSPROCCOL tables. The cspas4.sql script creates this table during installation of DirectConnect.
This function corresponds to the ODBC function SQLProcedureColumns.

Results

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. The following table shows the result set.

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 NULLID.SYSPROCCOL 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 if the procedure column is an input parameter 3 SQL_RESULT_COL if 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 means the column does not accept NULL 1 means the column accepts NULL 2 means it is not known if the column accepts NULL values
REMARKS	varchar(254)	Description of the procedure column

For a general description of AS/400 stored procedures, see "General description" .


	Expand Search