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 nameData typeDescription
PARAMETER_NAMEsysnameName of the parameter that SQL Server found in the batch.
COLUMN_TYPEsmallintThis field returns one of the following values:







This column is always 0.
DATA_TYPEsmallintData 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_NAMEsysnameString representation of the data type as it is presented by the underlying DBMS. This value is NULL.
PRECISIONintNumber of significant digits. The return value for the PRECISION column is in base 10.
LENGTHintTransfer size of the data. This value is NULL.
SCALEsmallintNumber of digits to the right of the decimal point. This value is NULL.
RADIXsmallintIs the base for numeric types. This value is NULL.
NULLABLEsmallintSpecifies nullability:

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

0 = Null values are not allowed.

This value is NULL.
SQL_DATA_TYPEsmallintValue 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_SUBsmallintThe 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_LENGTHintMaximum 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_POSITIONintOrdinal 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;  

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

Community Additions