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?