SQL Server Performance

problem with sp:recompile

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by thanneerukiran, Mar 12, 2007.

  1. thanneerukiran New Member

    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
  2. MohammedU New Member

  3. mmarovic Active Member

    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.

Share This Page