Recompiling Stored Procedure In SQL Serer 2008

In this article I will explain how to recompile a stored procedure using WITH RECOMPILE option in SQL Server.
  • 1428

In this article I will explain how to recompile a stored procedure using WITH RECOMPILE option in SQL Server. Stored procedure are precompiled. we don't need to compile again these stored procedure at using time. It makes stored procedure faster than execution of normal SQL statement.

When the stored procedure is executed for the first time (without using WITH RECOMPILE option), SQL Server recorded the query execution plan for Stored Procedure. This improves the performance and saves time. But if a query is dynamic i.e. each time result would vary, then use WITH RECOMPILE option.

There are two options to use WITH RECOMPILE option.

Create stored procedure using WITH RECOMPILE option. Just add the keyword WITH RECOMPILE when you create the stored procedure. Lets see an example.

create procedure sp_recompile

(

@StartDate date,

@EndDate date

)

WITH RECOMPILE

as

select * from [person].[dbo].[Employee]

where Joining between @StartDate and @EndDate

Execute the stored procedure using WITH RECOMPILE option in exec command.

RecompileOption.jpg

© 2013 dotNetheaven. All rights reserved.