sys.numbered_procedure_parameters (Transact-SQL)

Contains one row for each parameter of a numbered procedure. When you create a numbered stored procedure, the base procedure is number 1. All subsequent procedures have numbers 2, 3, and so forth. sys.numbered_procedure_parameters contains the parameter definitions for all subsequent procedures, numbered 2 and greater. This view does not show parameters for the base stored procedure (number = 1). The base stored procedure is similar to a nonnumbered stored procedure. Therefore, its parameters are represented in sys.parameters (Transact-SQL).

Important

Numbered procedures are deprecated in SQL Server 2005. Use of numbered procedures is discouraged. A DEPRECATION_ANNOUNCEMENT event is fired when a query that uses this catalog view is compiled.

Note

XML and CLR parameters are not supported for numbered procedures.

Column name Data type Description

object_id

int

ID of the object to which this parameter belongs.

procedure_number

smallint

Number of this procedure within the object, 2 or greater.

name

sysname

Name of the parameter. Is unique within procedure_number.

parameter_id

int

ID of the parameter. Is unique within the procedure_number.

system_type_id

tinyint

ID of the system type of the parameter

user_type_id

int

ID of the type, as defined by user, of the parameter.

max_length

smallint

Maximum length of the parameter in bytes.

-1 = Column data type is varchar(max), nvarchar(max), or varbinary(max).

precision

tinyint

Precision of the parameter if numeric-based; otherwise, 0.

scale

tinyint

Scale of the parameter if numeric-based; otherwise, 0.

is_output

bit

1 = Parameter is output or return; otherwise, 0

is_cursor_ref

bit

1 = Parameter is a cursor-reference parameter.

Note

XML and CLR parameters are not supported for numbered procedures.

See Also

Reference

Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance