PARAMETERS (Transact-SQL)

PARAMETERS (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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.

Column nameData typeDescription
SPECIFIC_CATALOGnvarchar(128)Catalog name of the routine for which this is a parameter.
SPECIFIC_SCHEMAnvarchar(128)Name of the schema of the routine for which this is a parameter.

 ** Important ** 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_NAMEnvarchar(128)Name of the routine for which this is a parameter.
ORDINAL_POSITIONintOrdinal position of the parameter starting at 1. For the return value of a function, this is a 0.
PARAMETER_MODEnvarchar(10)Returns IN if an input parameter, OUT if an output parameter, and INOUT if an input/output parameter.
IS_RESULTnvarchar(10)Returns YES if indicates result of the routine that is a function. Otherwise, returns NO.
AS_LOCATORnvarchar(10)Returns YES if declared as locator. Otherwise, returns NO.
PARAMETER_NAMEnvarchar(128)Name of the parameter. NULL if this corresponds to the return value of a function.
DATA_TYPEnvarchar(128)System-supplied data type.
CHARACTER_MAXIMUM_LENGTHintMaximum length in characters for binary or character data types.

-1 for xml and large-value type data. Otherwise, returns NULL.
CHARACTER_OCTET_LENGTHintMaximum length, in bytes, for binary or character data types.

-1 for xml and large-value type data. Otherwise, returns NULL.
COLLATION_CATALOGnvarchar(128)Always returns NULL.
COLLATION_SCHEMAnvarchar(128)Always returns NULL.
COLLATION_NAMEnvarchar(128)Name of the collation of the parameter. If not one of the character types, returns NULL.
CHARACTER_SET_CATALOGnvarchar(128)Catalog name of the character set of the parameter. If not one of the character types, returns NULL.
CHARACTER_SET_SCHEMAnvarchar(128)Always returns NULL.
CHARACTER_SET_NAMEnvarchar(128)Name of the character set of the parameter. If not one of the character types, returns NULL.
NUMERIC_PRECISIONtinyintPrecision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.
NUMERIC_PRECISION_RADIXsmallintPrecision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.
NUMERIC_SCALEtinyintScale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.
DATETIME_PRECISIONsmallintPrecision in fractional seconds if the parameter type is datetime or smalldatetime. Otherwise, returns NULL.
INTERVAL_TYPEnvarchar(30)NULL. Reserved for future use.
INTERVAL_PRECISIONsmallintNULL. Reserved for future use.
USER_DEFINED_TYPE_CATALOGnvarchar(128)NULL. Reserved for future use.
USER_DEFINED_TYPE_SCHEMAnvarchar(128)NULL. Reserved for future use.
USER_DEFINED_TYPE_NAMEnvarchar(128)NULL. Reserved for future use.
SCOPE_CATALOGnvarchar(128)NULL. Reserved for future use.
SCOPE_SCHEMAnvarchar(128)NULL. Reserved for future use.
SCOPE_NAMEnvarchar(128)NULL. Reserved for future use.

System Views (Transact-SQL)
Information Schema Views (Transact-SQL)
sys.columns (Transact-SQL)
sys.objects (Transact-SQL)
sys.parameters (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft