Microsoft® SQL Server 2012 Unleashed

Forcing Recompilation of Query Plans

In some situations, a stored procedure might generate different query plans, depending on the parameters passed in. At times, depending on the type of query and parameter values passed in, it can be difficult to predict the best query plan for all executions. Consider the following stored procedure:

Click here to view code image

IF EXISTS ( SELECT * FROM sys.procedures               WHERE schema_id= schema_id('dbo')                 AND name = N'price_range')   DROP PROCEDURE dbo.price_rangeGOcreate proc price_range    (@low money, @high money)asselect * from Production.Product  where ListPrice between @low and @highreturn

Assume that a nonclustered index exists on the ListPrice column in the Product table. ...