Search with stored procedure and multiple, optional criteria

Common need and question I'm sure...I'd like to use a stored procedure
to return results where the search criteria will vary quite a bit.
Someone may invoke search with or without all of the possible
parameters. I have a solution working ok that follows the approach shown
in a thread 'Complex Select Query Using Stored Procedure' April 7 2002
but I'm not sure it's the most efficient. If I build the sql
dynamically, will performance improve? The approach below (quoted from
newgroup posting mentioned above) seems kind of strange because it needs
to eval all of the fields, even if they are not involved in the
particular search. But if dynamic sql, ie build sql statement that uses
only the needed criteria, will it be optimized?

Below is a lot like what I'm doing to date:

use pubs
go

create procedure Search
@FirstName varchar (20) = NULL,
@LastName varchar (40) = NULL,
@State char (2) = NULL
as
select *
from authors
where (@FirstName IS NULL or au_fname = @FirstName)
and   (@LastName IS NULL or au_lname = @LastName)
and   (@State IS NULL or state = @State)
go

exec Search @FirstName = 'Albert'
exec Search @LastName = 'Ringer'
exec Search @State = 'MD'
exec Search @FirstName = 'Dirk', @State = 'CA'

Search with stored procedure and multiple, optional criteria

In article <MPG.21f305d373558a619896ab@msnews.microsoft.com>,
sandal@sandal.org says...
Hmmm, I'd found that example as a shortcut to laying out my own
solution. But it's more different than I though on closer eval, maybe
better. With criteria like

where (@FirstName IS NULL or au_fname = @FirstName)

is the field value ever evaluated if @FirstName is null? If not, then
probably better than what I'd built. Is it a common and efficient
approach?

Search with stored procedure and multiple, optional criteria

sandal (sandal@sandal.org) writes:

Yes. Your example is OK if the dataset is small, and it's OK to scan
the table. But it's not very efficient. Which has not so much to do with
that all columns need to be evaluated, but the fact that no index can
be used. With dynamic SQL, SQL Server will pick the indexes are good for
that precise search.o

I have an article on my web site that discusses this problem in depth,
and covers the method you posted, dynamic SQL and a few more tricks.
http://www.sommarskog.se/dyn-search.html.

--
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

I use this approach all the time.

I use this approach all the time. It's a whole lot easier to read and
more effiecient than dynamic sql or a bunch of IF statements. You just
have to be careful that the default value of the parameter is never
going to find an equal in the database. If it's an identity column, I
set the parameter = 0.

Oh, I forgot to answer your second question.

Oh, I forgot to answer your second question. Yes, both sides are
evaluated, so, again, just find a value that will never be in the
database, such as au_fname = "***"

Search with stored procedure and multiple, optional criteria

bean (dbvann@product-key.com) writes:

Look at the procedure again:

create procedure Search
@FirstName varchar (20) = NULL,
@LastName varchar (40) = NULL,
@State char (2) = NULL
as
select *
from authors
where (@FirstName IS NULL or au_fname = @FirstName)
and   (@LastName IS NULL or au_lname = @LastName)
and   (@State IS NULL or state = @State)

SQL Server builds the query plan for the procedure when it's first
executed and puts the plan in cache. The query plan must work for all input.
Say that on the first call, the user specifies @FirstName and there
is an index on that column. What if the optimizer uses that index, and
the next user asks about state? How useful is the index on au_fname in
this case?

If you add the option WITH RECOMPILE to the procedure definition or
the EXEC statement, there will be a new plan each time. Ah, but in
this case, it could use the index? No. The optimizer does not perform
any flow analysis, so it does not know what the values of the parameters
will be at run time. So again, it must scan the table to ensure a
correct result.

In SQL 2005, there is also an statement-level hint OPTION(RECOMPILE)
which forces statement recompilation each time. Now, with this option
in force, the optimizer should really be able to use the indexes, right?
Nothing will be put in the cache, and the variables cannot change values
during the query. That is perfectly correct, but there still is a snag:
the SQL Server developers apparently overlooked this possibility, and
uses the same optimisation scheme as for a procedure with parameters.

I submitted an item on Connect some time back, and it was closed as
Postponed. As still works the same in the most recent CTP of SQL 2008,
I just reopened it.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=244298



--
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

Why does SQL Server do an index scan instead of a seek when using theOR clause

Why does SQL Server do an index scan instead of a seek when using the
OR clause in such a way?