How to get Query Plan Reuse in MS SQL Server

Question

I inherited a database application that has a table with about 450 queries. There's a calling procedures takes the @QueryId and @TheId as input parameters. The only way these queries are executed is via this procedure. The queries are like this:

@sql = replace('insert into #temp select col1, col2, col3, col4
from SomeTable st join OtherTable ot on matching_column
where st.TheID = ##TheId##', '##TheId##', @TheId);

exec sp_executesql @sql;

I want to get plan reuse, so I replace ##TheId## with @TheId and then execute the query like this:

exec sp_executesql @sql, N'@TheId int', @TheId;

However, I'm still seeing the same behavior where each plan is a unique plan, even though the @sql string is already compiled and in the procedure cache.

Now the string is like this

...where where st.TheID = @TheId

Question: how can I get plan reuse as desired on a parameterized query?

Answer 1

Well if you modify it to the following you should get plan reuse as this will make it a parameterized query:

@sql = replace('insert into #temp select col1, col2, col3, col4
from SomeTable st join OtherTable ot on matching_column
where st.TheID = ##TheId##', '##TheId##', '@TheId');

exec sp_executesql @sql, N'@TheID INT', @TheID;

https://technet.microsoft.com/en-us/library/ms175580(v=sql.105).aspx