Export (0) Print
Expand All
Expand Minimize

sp_get_query_template (Transact-SQL)

Returns the parameterized form of a query. The results returned mimic the parameterized form of a query that results from using forced parameterization. sp_get_query_template is used primarily when you create TEMPLATE plan guides.

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

Topic link icon Transact-SQL Syntax Conventions

sp_get_query_template
   [ @querytext = ] N'query_text'
   , @templatetext OUTPUT 
   , @parameters OUTPUT 

'query_text'

Is the query for which the parameterized version is to be generated. 'query_text' must be enclosed in single quotation marks and be preceded by the N Unicode specifier. N'query_text' is the value assigned to the @querytext parameter. This is of type nvarchar(max).

@templatetext

Is an output parameter of type nvarchar(max), provided as indicated, to receive the parameterized form of query_text as a string literal.

@parameters

Is an output parameter of type nvarchar(max), provided as indicated, to receive a string literal of the parameter names and data types that have been parameterized in @templatetext.

sp_get_query_template returns an error when the following occur:

  • It does not parameterize any constant literal values in query_text.

  • query_text is NULL, not a Unicode string, syntactically not valid, or cannot be compiled.

If sp_get_query_template returns an error, it does not modify the values of the @templatetext and @parameters output parameters.

Requires membership in the public database role.

The following example returns the parameterized form of a query that contains two constant literal values.

USE AdventureWorks2012;
GO
DECLARE @my_templatetext nvarchar(max)
DECLARE @my_parameters nvarchar(max)
EXEC sp_get_query_template 
    N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total
        FROM Production.ProductModel pm 
        INNER JOIN Production.ProductInventory pi
        ON pm.ProductModelID = pi.ProductID
        WHERE pi.ProductID = 2
        GROUP BY pi.ProductID, pi.Quantity
        HAVING SUM(pi.Quantity) > 400',
@my_templatetext OUTPUT,
@my_parameters OUTPUT;
SELECT @my_templatetext;
SELECT @my_parameters;

Here are the parameterized results of the @my_templatetext OUTPUT parameter:

select pi . ProductID , SUM ( pi . Quantity ) as Total

from Production . ProductModel pm

inner join Production . ProductInventory pi

on pm . ProductModelID = pi . ProductID

where pi . ProductID = @0

group by pi . ProductID , pi . Quantity

having SUM ( pi . Quantity ) > 400

Note that the first constant literal, 2, is converted to a parameter. The second literal, 400, is not converted because it is inside a HAVING clause. The results returned by sp_get_query_template mimic the parameterized form of a query when the PARAMETERIZATION option of ALTER DATABASE is set to FORCED.

Here are the parameterized results of the @my_parameters OUTPUT parameter:

@0 int
NoteNote

The order and naming of parameters in the output of sp_get_query_template can change between quick-fix engineering, service pack, and version upgrades of SQL Server. Upgrades can also cause a different set of constant literals to be parameterized for the same query, and different spacing to be applied in the results of both output parameters.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft