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

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.