Bryan Smith (1/6/2009)
Adam I agree with your statement on using Control Logic (and great article btw). I'm curious though, using this method have you run into problems with queries plans. What I mean is for example when the stored procedure is first ran it gets compiled with the query plan for the case where FirstName is null and LastName is what is index on; so subsequent times when FirstName is not null a unoptimal query plan is used.
The solutions I know of are to have the procedure recompile each time it is used, thus a new query plan or use your control logic to call out other stored procedures that have just one query in them. I've prefered the 2nd option in the past, as I prefer the finite control and want to skip the hit of recompiling. But perhaps you know of a better solution?
Well you are partially correct in your assumption, but the stored procedure should not recompile every time. What you should see is a query plan for each set of options, in this case there are three. A first name and no last name, no first name and a last name, and finally no first name and no last name. For example, if you supply first name and no last name a query plan will be created. The next go around you supply no first name and a lastname.. this will result in a new query plan. You will be able to reuse the query plan for first name and no last name every time you execute the stored procedure with those parameters and likeswise for no first name and a last name. This means you should be reusing query plans quite often.
You can use the code below to watch the execution plan count increase for each run.
(CASE WHEN qs.statement_end_offset = -1
THEN len(CONVERT(nvarchar(max), qt.text)) * 2
END -qs.statement_start_offset)/2) AS query_text,
FROM sys.dm_exec_query_stats qs
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE db_name(qt.dbid) = 'tempdb'
WHERE query_text LIKE ';WITH PagingCTE%'
ORDER BY execution_count DESC