When you use exec() it will almost always generate a new execution plan (never reuse an execution plan), while sp_executesql will cache and reuse an execution plan based on the parameters sent to the SP.

Is it possible to make the sp_executesql never cache the execution plan? Or maybe recompile it every time?

I want the good things with parameterized queries (to avoid sql-injection etc), but I always want to generate a new execution plan.

I have a table on which I make searches on username. If you do a search like 'brimba%' a certain execution plan will be made, but if you have alot of posts in the table and you do a search on 'b%' most probably sql-server will pick another execution plan.

I have tried to add a WITH RECOMPILE to the SP but it does not seem to work.

I am using sql 2000, so I cannot use the sql 2005 feature OPTION (RECOMPIL) (dont know if it even would work?)

Any other suggestions?

I don't see any problem changing the values...and I don't see any reason to recomplie it..because I believe SQL only caches the plan not the values.





Mohammed U.

Yes sql caches the plan, so lets say I make a search for 'b%', then it will probably not use any index since the range of found posts is so big, so a scan would probably be more efficient, but if I search for 'brimba%' it will probably be very few rows and the index can be used.
But if the plan with 'b%' is caches then it will do a table scan even when I search for 'brimba%'.

No, sql uses the index as long there is no wild card before the character...

If you put the wild card befor sql will not use the index...

select * from sysobjects where name = '%obj%' -- index will not be used...
select * from sysobjects where name = 's%' -- index will be used...



Mohammed U.

We are talking about different things. I never mentioned putting a wild card in front.
Please read my posts again.