SQL Server Performance Forum – Threads Archive

problem with sp:recompile

HI,
I written the stored procedure with dynamic sql and tried to executed. When i executed above sp, it is not comming under profiler sp:recompile event. According my knowledge if stored procedure has a dynamic sql then it should be recompiled. Is there any other setting we need to do? Alter proc spDynamic WITH RECOMPILE
as
declare @sql varchar(1000),
@caseID varchar(10)
set @empID = 20000111 set @sql = ‘select top 1 * from cacase where employee= ‘ + @empID exec(@sql)
kIRAN
SQL DBA

---

Why you need dynamic sql for this proc or this is just an example you are providing us? Read the following article for complete details…
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#EDOAC
MohammedU.
Moderator
SQL-Server-Performance.com

---

Stored procedure is not recompiled, because there is no dataset operation to compile (build execution plan) just exec (string). On the other hand, exec(string) will cause the compilation the first time it runs, but it is not sp compilation, just statement contained in string compilation. Execution plan of that statment will be in the cache and it won’t recompile the way it is written, because the query string is always the same. However, if you change stored procedure to have @empId as its parameter and execute the string with concatenation of @empId instead of using sp_ExecuteSQL with @empId as a parameter, then dynamic sql statetment will be recompile each time the procedure is executed with different parameter value then previously. Read carefully article posted by Mohammed.

---

]]>