Posted by Erland Sommarskog on 12/07/05 09:59

(JayCallas@hotmail.com) writes:
> As you can see, I did the left-join so the user can pass null and get
> everything or they can ask for specific symbols.
>
> As to having different queries, what is the communities opinion about
> having two queries in the same stored procedure and using an IF
> statement to branch to one of them? Does that play havoc with the
> execution plan since either query can be called? (I could create
> another stored procedure to handle ALL securities but I already have
> applications out there that use my existing one.)

There is a risk at some occassions due to parameter sniffing. If you have
say:

IF @x IS NOT NULL
SELECT ... FROM tbl WHERE x = @x
ELSE IF @y IS NOT NULL
SELECT ... FROM tbl WHERE y = @y

Say that first time the procedure is called with a value @x in. The plan
will then be built with the assumption that @y is NULL, which may not be
the best.

One way to handle this, is to have sub-procedures for each query.
Admittedly, this is not very manageable. Another way is to use dynamic
SQL, and I have an article on my web site about this:
http://www.sommarskog.se/dyn-search.html.

In this particular case, I don't think there is any reason for concern
though, as the optimizer is not likely to be able to make anything useful
from the parameter with the list.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]