jdk77 (5/1/2014)

---

Hi

I have a C# solution with many parameterized sql queries.

I would like to be able to add optimizations to these in a centralized way.

For the sake of simplicity, let's say I want to add the option to OPTIMIZE FOR UNKNOWN for all the queries (in practice I might want to add different optimzations or none to selected queries)...

I've experimented with a query in SSMS and it seems that if you have two selects (so sp_executesql is executing sql that has two selects), then it matters where you insert "OPTION (OPTIMIZE FOR UNKNOWN)".

This is probably all fine and good, but I want to make sure that all statements uses the optimize for unknown option.

How can I (without too much work) make sure that all sql in my parameterized calls uses the option?

I would like to avoid having to edit every query, but instead have one (or a few) central places in my code where I can manipulate the queries.

I was thinking that a simple solution would be to add the option to the end of the commandtext, but some queries have multiple selects (eg. first an insert into a temporary table and then a select all from that temporary table, in which case I believe the option should have been inserted somewhere inside the commandtext to have an effect).

It seems like an inefficient method, if I have to analyze the commandtext and insert multiple option statements...