sp_batch_params (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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

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

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 AdventureWorks2012.HumanResources.Employee   
     WHERE BusinessEntityID = @BusinessEntityID';  
EXECUTE sp_batch_params @SQLString;  

See Also

Running Stored Procedures
Running Stored Procedures How-to Topics (ODBC)
Running Stored Procedures (OLE DB)