Share via

  • Article

sp_batch_params (Transact-SQL)

Returns a rowset that contains information about the parameters included in a Transact-SQL batch. sp_batch_params only parses the batch specified and returns information about embedded parameter values. It does not execute the batch or modify the execution environment.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_batch_params [ [ @tsqlbatch = ] 'tsqlbatch' ]

Arguments

  • [ @tsqlbatch =] 'tsqlbatch'
    Is a Unicode string that contains a Transact-SQL statement or batch for which parameter information is that you want. tsqlbatch is nvarchar(max) or implicitly convertible to nvarchar(max).

Return Code Values

None

Result Sets

Column name

Data type

Description

PARAMETER_NAME

sysname

Name of the parameter that SQL Server found in the batch.

COLUMN_TYPE

smallint

This field always returns one of the following values:

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

In SQL Server 2005 and later, this column is always 0.

DATA_TYPE

smallint

Data type of the parameter (Integer code for an ODBC data type). If this data type cannot be mapped to an ISO type, the value is NULL. The native data type name is returned in the TYPE_NAME column. In SQL Server 2005 and later, this value is NULL.

TYPE_NAME

sysname

String representation of the data type as it is presented by the underlying DBMS. In SQL Server 2005 and later, this value is NULL

PRECISION

int

Number of significant digits. The return value for the PRECISION column is in base 10.

LENGTH

int

Transfer size of the data. In SQL Server 2005 and later, this value is NULL.

SCALE

smallint

Number of digits to the right of the decimal point. In SQL Server 2005 and later, this value is NULL.

RADIX

smallint

Is the base for numeric types. In SQL Server 2005 and later, this value is NULL.

NULLABLE

smallint

Specifies nullability:

1 = Parameter data type can be created allowing null values.

0 = Null values are not allowed.

In SQL Server 2005 and later, this value is NULL.

SQL_DATA_TYPE

smallint

Value of the SQL Server system data type as it appears in the TYPE field of the descriptor. This column is the same as the DATA_TYPE column, except for the datetime and ISO interval data types. This column always returns a value. In SQL Server 2005 and later, this value is NULL.

SQL_DATETIME_SUB

smallint

The datetime or ISO interval subcode if the value of SQL_DATA_TYPE is SQL_DATETIME or SQL_INTERVAL. For data types other than datetime and ISO interval, this column is NULL. In SQL Server 2005 and later, this value is NULL.

CHAR_OCTET_LENGTH

int

Maximum length in bytes of a character or binary data type parameter. For all other data types, this column returns a NULL. In SQL Server 2005 and later, this value is always NULL.

ORDINAL_POSITION

int

Ordinal position of the parameter in the batch. If the parameter name is repeated multiple times, this column contains the ordinal of the first occurrence. The first parameter has ordinal 1. This column always returns a value.

Permissions

Permission to execute sp_batch_params is granted to public.

Examples

The following example shows a query being passed to sp_batch_params. The result set enumerates the list of embedded parameter values.

DECLARE @SQLString nvarchar(500);
/* Build the SQL string */
SET @SQLString =
     N'SELECT * FROM AdventureWorks.HumanResources.Employee 
     WHERE ManagerID = @ManagerID';
EXECUTE sp_batch_params @SQLString