sp_describe_undeclared_parameters is a new system stored procedure added in SQL Server 2012 (Denali). It returns a result set that contains metadata about undeclared parameters in a Transact-SQL batch.

The following excerpt is taken from MSDN documentation

The most common use is when an application is given a Transact-SQL statement that might contain parameters and must process them in some way. An example is a user interface (such as ODBCTest or RowsetViewer) where the user provides a query with ODBC parameter syntax. The application must dynamically discover the number of parameters and prompt the user for each one.

Another example is when without user input, an application must loop over the parameters and obtain the data for them from some other location (such as a table). In this case, the application does not have to pass all the parameter information at once. Instead, the application can get all the parameters information from the provider and obtain the data itself from the table. Code using sp_describe_undeclared_parameters is more generic and is less likely to require modification if the data structure changes later.structure changes later.

The following example demonstrates how to use this procedure.

-- Build a query string that contains parameters
DECLARE @query NVARCHAR(MAX) = N'
SELECT 
    object_id, name, type_desc 
FROM sys.indexes
WHERE object_id = @id OR name = @name'

-- Retrieve metadata of @id and @name parameters
EXECUTE sp_describe_undeclared_parameters 
    @tsql = @query

• Reference 1  [17 clicks]
• Reference 2  [32134 clicks]
• Reference 3  [32134 clicks]

Category :	Database Administration
Release :	RC0
Tags :	SQL Server 2012, Denali, Database Administration, DBA, System stored procedures, Metadata Discovery