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?