2

I have a query that is executed using sp_executesql

code is as follows

DECLARE @rsk NVARCHAR(1) = '1', @ppCityID NVARCHAR(3);
SET @ppCityID = CAST(@cityID AS NVARCHAR(3));

exec sp_executesql N'Insert Into #FeaturedAdIDs Select 
    fa.AdID,fa.PreviousHourViewCount,fa.IsFromCP,fa.SubCategoryID 
    from FeaturedAds fa 
            INNER JOIN #FilteredFeaturedAdIDs ffa 
            ON fa.AdID = ffa.AdID 
                    and fa.SubCategoryID = ffa.SubCategoryID 
                    and fa.RecordStatusKey = @pRsk 
    Where fa.CityID = @pCityID'
    , N'@pRsk INT, @pCityID INT'
    , @pRsk = @rsk
    , @pCityID = @ppCityID;

This piece of code is inside a stored proc.

When I look at the sys.dm_exec_cached_plans it shows that every time I execute this query, with OR without the same parameters, it keeps increasing the plan_generation_num and the exec_count stays at 1.

How can I stop recompiles of this statement?

1
  • I'm pretty sure you can't because of the temporary tables in the query.
    – Jon Seigel
    Nov 11, 2013 at 16:23

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.