Export (0) Print
Expand All
Expand Minimize

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.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

sp_batch_params [ [ @tsqlbatch = ] 'tsqlbatch' ] 

[ @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).

Column name

Data type

Description

PARAMETER_NAME

sysname

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

COLUMN_TYPE

smallint

This field 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

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. This value is always NULL.

TYPE_NAME

sysname

String representation of the data type as it is presented by the underlying DBMS. 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. This value is NULL.

SCALE

smallint

Number of digits to the right of the decimal point. This value is NULL.

RADIX

smallint

Is the base for numeric types. This value is NULL.

NULLABLE

smallint

Specifies nullability:

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

0 = Null values are not allowed.

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. 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. 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. 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.

Permission to execute sp_batch_params is granted to public.

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 AdventureWorks2012.HumanResources.Employee 
     WHERE BusinessEntityID = @BusinessEntityID';
EXECUTE sp_batch_params @SQLString;
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft