
USE AdventureWorks
GO

sp_control_plan_guide N'DROP', N'GuideOptForUS'
GO

DROP PROCEDURE Sales.GetSalesOrderByCountry2
GO

CREATE PROCEDURE Sales.GetSalesOrderByCountry2 (@Country nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader h, Sales.Customer c, 
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country
END
GO

sp_create_plan_guide 
@name = N'GuideOptForUS',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h,
        Sales.Customer c,
        Sales.SalesTerritory t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry2',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))'
