Query Recompilation - SQL Server Query Performance Tuning - page 330

RECOMPILE Clause with the CREATE PROCEDURE Statement
Sometimes the plan requirements of a stored procedure will vary as the parameter values to the stored procedure
change. In such a case, reusing the plan with different parameter values may degrade the performance of the stored
procedure. You can avoid this by using the RECOMPILE clause with the CREATE PROCEDURE statement. For example,
for the query in the preceding section, you can create a stored procedure with the RECOMPILE clause.
IF (SELECT OBJECT_ID('dbo.CustomerList')
) IS NOT NULL
DROP PROC dbo.CustomerList;
GO
CREATE PROCEDURE dbo.CustomerList @CustomerId INT
WITH RECOMPILE
AS
SELECT soh.SalesOrderNumber,
soh.OrderDate,
sod.OrderQty,
sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID >= @CustomerId;
GO
The RECOMPILE clause prevents the caching of the stored procedure plan for every statement within the
procedure. Every time the stored procedure is executed, new plans are generated. Therefore, if the stored procedure is
executed with the soh.CustomerID value as 30118 or 1
EXEC CustomerList
@CustomerId = 1;
EXEC CustomerList
@CustomerId = 30118;
a new plan is generated during the individual execution, as shown in Figure 17-12 .
Figure 17-12. Effect of the RECOMPILE clause used in stored procedure creation