sp_get_query_template (Transact-SQL)

Applies to: SQL Server

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.

Transact-SQL syntax conventions

Syntax

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

Arguments

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

Remarks

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.

Permissions

Requires membership in the public database role.

Examples

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

USE AdventureWorks2022;  
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  

Note

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.

See Also

System Stored Procedures (Transact-SQL)
Database Engine Stored Procedures (Transact-SQL)
Specify Query Parameterization Behavior by Using Plan Guides