I recently received an email from reader after reading my previous article on SQL SERVER – Plan Recompilation and Reduce Recompilation – Performance Tuning regarding how to recompile any stored procedure at run time. There are multiple ways to do this. If you want your stored procedure to always recompile at run time, you can add the keyword RECOMPILE when you create the stored procedure. Additionally, if the stored procedure has to be recompiled at only one time, in that case, you can add RECOMPILE word one time only and run the SP as well. Let us go over these two options.
The RECOMPILE hint is used with a query and recompiles only that particular query. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution.
CREATE PROCEDURE dbo.PersonAge (@MinAge INT, @MaxAge INT) WITH RECOMPILE AS SELECT * FROM dbo.tblPerson WHERE Age >= @MinAge AND Age <= @MaxAge GO
EXEC dbo.PersonAge 65,70 WITH RECOMPILE
This method is not recommended for large stored procedures because the recompilation of so many statements may outweigh the benefit of a better execution plan.
Note: Recompilation is not necessarily the best solution.
Reference: Pinal Dave (http://blog.SQLAuthority.com)