Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.