Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

PARAMETERS (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database. For functions, this view also returns one row with return value information.

To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA.view_name.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Column name

Data type

Description

SPECIFIC_CATALOG 

nvarchar(128)

Catalog name of the routine for which this is a parameter.

SPECIFIC_SCHEMA 

nvarchar(128)

Name of the schema of the routine for which this is a parameter.

System_CAPS_importantImportant

Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.

SPECIFIC_NAME 

nvarchar(128)

Name of the routine for which this is a parameter.

ORDINAL_POSITION 

int

Ordinal position of the parameter starting at 1. For the return value of a function, this is a 0.

PARAMETER_MODE 

nvarchar(10)

Returns IN if an input parameter, OUT if an output parameter, and INOUT if an input/output parameter.

IS_RESULT 

nvarchar(10)

Returns YES if indicates result of the routine that is a function. Otherwise, returns NO.

AS_LOCATOR 

nvarchar(10)

Returns YES if declared as locator. Otherwise, returns NO.

PARAMETER_NAME 

nvarchar(128)

Name of the parameter. NULL if this corresponds to the return value of a function.

DATA_TYPE 

nvarchar(128)

System-supplied data type.

CHARACTER_MAXIMUM_LENGTH 

int

Maximum length in characters for binary or character data types.

-1 for xml and large-value type data. Otherwise, returns NULL.

CHARACTER_OCTET_LENGTH 

int

Maximum length, in bytes, for binary or character data types.

-1 for xml and large-value type data. Otherwise, returns NULL.

COLLATION_CATALOG 

nvarchar(128)

Always returns NULL.

COLLATION_SCHEMA 

nvarchar(128)

Always returns NULL.

COLLATION_NAME 

nvarchar(128)

Name of the collation of the parameter. If not one of the character types, returns NULL.

CHARACTER_SET_CATALOG 

nvarchar(128)

Catalog name of the character set of the parameter. If not one of the character types, returns NULL.

CHARACTER_SET_SCHEMA 

nvarchar(128)

Always returns NULL.

CHARACTER_SET_NAME 

nvarchar(128)

Name of the character set of the parameter. If not one of the character types, returns NULL.

NUMERIC_PRECISION 

tinyint

Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.

NUMERIC_PRECISION_RADIX 

smallint

Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.

NUMERIC_SCALE 

tinyint

Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.

DATETIME_PRECISION 

smallint

Precision in fractional seconds if the parameter type is datetime or smalldatetime. Otherwise, returns NULL.

INTERVAL_TYPE 

nvarchar(30)

NULL. Reserved for future use.

INTERVAL_PRECISION 

smallint

NULL. Reserved for future use.

USER_DEFINED_TYPE_CATALOG 

nvarchar(128)

NULL. Reserved for future use.

USER_DEFINED_TYPE_SCHEMA 

nvarchar(128)

NULL. Reserved for future use.

USER_DEFINED_TYPE_NAME 

nvarchar(128)

NULL. Reserved for future use.

SCOPE_CATALOG 

nvarchar(128)

NULL. Reserved for future use.

SCOPE_SCHEMA 

nvarchar(128)

NULL. Reserved for future use.

SCOPE_NAME 

nvarchar(128)

NULL. Reserved for future use.

Community Additions

ADD
Show:
© 2015 Microsoft