sp_sproc_columns

Description

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	This field always returns a value: 0 = SQL_PARAM_TYPE_UNKNOWN 1 = SQL_PARAM_TYPE_INPUT 2 = SQL_PARAM_TYPE_OUTPUT 3 = SQL_RESULT_COL 4 = SQL_PARAM_OUTPUT 5 = SQL_RETURN_VALUE
  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 Adaptive Server datatype.
  colid	tinyint	The column ID from syscolumns.
  column_def	varchar(255)	NULL.
  sql_data_type	smallint	An Adaptive Server 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 Adaptive Server 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.

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF