sp_sproc_columns

Returns information about a stored procedure’s input and return parameters.

Syntax

sp_sproc_columns procedure_name [, procedure_owner] 
	[, procedure_qualifier] [, column_name]

Parameters

procedure_name – is the name of the stored procedure. The use of wildcard characters in pattern matching is not supported.
procedure_owner – is the owner of the stored procedure. The use of wildcard characters in pattern matching is not supported. If no owner is specified, sp_sproc_columns returns all columns.
procedure_qualifier – is the name of the database. This can be either the current database or NULL.
column_name – is the name of the parameter about which you want information. If you do not supply a parameter name, sp_sproc_columns returns information about all input and return parameters for the stored procedure.

Usage

The results set for sp_sproc_columns is:

Column	Datatype	Description
`procedure_qualifier`	`varchar(30)`	Procedure qualifier name. Can be NULL.
`procedure_owner`	`varchar(30)`	Procedure owner name. Always returns a value.
`procedure_name`	`varchar(41)`	Procedure name. Always returns a value.
`column_name`	`varchar(30)`	Column name for each column of the `table_name` returned. Always returns a value.
`column_type`	`smallint`
`data_type`	`smallint`	The integer code for an ODBC datatype. If this datatype cannot be mapped to an ANSI/ISO type, the value is NULL. The native datatype name is returned in the `type_name` column.
`type_name`	`char(30)`	The string representation of the datatype. This is the datatype name as presented by the underlying DBMS.
`precision`	`int`	The number of significant digits.
`length`	`int`	The length in bytes of the datatype.
`scale`	`smallint`	The number of digits to the right of the decimal point.
`radix`	`smallint`	The base for numeric types.
`nullable`	`smallint`	The value 1 means this datatype can be created allowing null values; 0 means it cannot.
`remarks`	`varchar(254)`	The description of the procedure column. NULL.
`ss_data_type`	`tinyint`	An SAP ASE datatype.
`colid`	`tinyint`	The column ID from `syscolumns`.
`column_def`	`varchar(255)`	NULL.
`sql_data_type`	`smallint`	An SAP ASE datatype.
`sql_datetime_sub`	`smallint`	NULL.
`char_octet_length`	`int`	The value of `char_octet_length` is the same as the value for the `precision` column if the datatype for `char_octet_length` is: `binary` `char` `image` `nchar` `nvarchar` `sysname` `text` `timestamp` `varbinary` `varchar` Otherwise, the value of `char_octet_length` is 0.
`ordinal_position`	`int`	The ordinal position of the parameter in the parameter list. The first parameter in the list is 1, and return values have an ordinal.
`is_nullable`	`varchar(3)`	Describes whether the column or parameter allows NULL as a value. From `syscolumns`.
`mode`	`varchar(20)`	The parameter mode information stored in `syscolumns` that contains: For SQL procedures – in, out, or “return value”. For SQLJ procedures (Java) – in, out, inout, or “return value”.

sp_sproc_columns reports the type_name as float, and data_type as 6 for parameters defined as double precision. The SAP ASE double precision datatype is a float implementation supports the range of values as specified in the ODBC specifications.

Permissions

Any user can execute sp_sproc_columns.