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 
    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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.