
USE AdventureWorks
GO

-- these could be parameterized, they are very similar
SELECT * 
FROM AdventureWorks.Sales.SalesOrderHeader h, 
     AdventureWorks.Sales.SalesOrderDetail d 
WHERE h.SalesOrderID = d.SalesOrderID AND h.SalesOrderID = 45639
GO

SELECT * 
FROM AdventureWorks.Sales.SalesOrderHeader h, 
     AdventureWorks.Sales.SalesOrderDetail d 
WHERE h.SalesOrderID = d.SalesOrderID AND h.SalesOrderID = 45640
GO

-- create a plan guide with a template for the parameter
sp_create_plan_guide 
@name = N'TemplateGuide1',
@stmt = N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader h, AdventureWorks.Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.SalesOrderID = @0',
@type = N'TEMPLATE',
@module_or_batch = NULL,
@params = N'@0 int',
@hints = N'OPTION(PARAMETERIZATION FORCED)'
GO

-- say you have the following query
-- you want to force parameterization
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 = 101 
GROUP BY pi.ProductID, pi.Quantity HAVING sum(pi.Quantity) > 50
GO

-- get query template
USE AdventureWorks;
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;


-- use it in plan guide
DECLARE @stmt nvarchar(max)
DECLARE @params 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 = 101 GROUP BY pi.ProductID, pi.Quantity HAVING sum(pi.Quantity) > 50',
@stmt OUTPUT, 
@params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide2', 
@stmt, 
N'TEMPLATE', 
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)'


