
USE AdventureWorks
GO

sp_control_plan_guide N'DROP', N'APICursorGuide'
GO

-- You see this in RPC:Starting
-- It's using a merge join
-- You think it should use a hash join

DECLARE @p1 int
SET @p1=-1
DECLARE @p2 int
SET @p2=0
DECLARE @p5 int
SET @p5=4104
DECLARE @p6 int
SET @p6=8193
DECLARE @p7 int
SET @p7=0
EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate BETWEEN @P1 AND @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7
GO

-- here's a plan guide to do it
exec sp_create_plan_guide 
@name = N'APICursorGuide',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate BETWEEN @P1 AND @P2',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@P1 varchar(255),@P2 varchar(255)',
@hints = N'OPTION(HASH JOIN)'
