SQL Server – Stored Procedure – WITH RECOMPILE

Stored Procedure has ability to execute faster than the same T-SQL statements executed individually because SQL Server recorded the query execution plan for Stored Procedure.

In case you need to execute the stored procedure with different execution plan then use WITH RECOMPILE options

Different ways to use the WITH RECOMPILE options

• You can create the stored procedure using WITH RECOMPILE option. This way every time SQL Server uses the recompile the execution plan.

CREATE PROCEDURE sp_orders
(
@StartDate Datetime,
@EndDate DateTime
)
WITH RECOMPILE
AS
SELECT *
FROM orders
WHERE OrderDate Between(@StartDate AND @EndDate)

• Execute the stored procedure using the WITH RECOMPILE option in the EXEC command.

EXEC sp_orders ‘1-Nov-2011’, ‘15-Nov-2011’ WITH RECOMPILE

Note:

• If we drop the stored procedure then re-create it. This way also SQL Server recompile the execution plan.
• If SQL Server is stopped and restarted, all execution plans are flushed from the procedure cache and recreated when the stored procedures are executed after restarting the server.