Recompiling Stored Procedure

Recompiling Stored Procedure

http://www.dotnetspider.com/resources/28544-Recompiling-Stored-Procedure.aspx

Recompiling a stored procedure is necessary when the stored procedure changes every time of calling. By the normal way of stored procedure execution, the stored procedure will calls from the execution plan. Normal execution doesn’t tries to create a new data access plan from the database, It tries to get the information from the execution plan. It works as a performance booster.
If the programmer thinks to compile the store procedure each and every time then, can use this method to recompile the stored procedure.
Recompiling for an Existing Stored Procedure
The existing stored procedure can be set for the recompiling from the upcoming executions.
Syntax :
Exec sp_recompile
This is for set the recompiling option for the existing stored procedures.
Creating Stored Procedure with Recompiling option
We can set the recompiling option while creating the stored procedure. If we create stored procedure with recompiling option sql server doesn’t get the execution plan for this stored procedure. Sql Server compile this each and every time of execution.
Use WITH RECOMPILE option if the stored procedure has parameters. The stored procedure result will vary based on the parameter passed. The stored procedure which has parameter should be recompile for the parameter based results.
Syntax
Create Procedure Precuderenam e With Recompile
As
Sql Statements

Create Procedure MyProcedure WITH RECOMPILE

AS 

SELECT * FROM EMPLOYEE 

GO 

One Time Recompiling
We can execute the stored procedure with this recompiling option. This kind of execution is for one time only. If next time we call the procedure without recompiling, the stored procedure will execute from the execution plan. If we need each and every time then go for the first method. This method is forced the server for recompiling the stored procedure.
Syntax
EXEC MyProcedure WITH RECOMPILE