Optional Parameters in SQL Server Search Queries

I recently was discussing some strategies for performing searches against database tables where a set of optional parameters can be supplied. The scenario I'm talking about is in regards to doing searches on a table based on a set of ID's within the table, not free text searches. The individual that I was discussing this with was not looking forward to writing the proc to perform the search becuase there was approximately 10 optional criteria. I proded a little to see what the problem was and found that he was planning on implementing the optional criteria by having a bunch of if/else if statements checking each of the criteria. Rather then going that route, I suggested an alternative technique.

Assume we have a table called Employee with the following fields:

EmployeeID int
DepartmentID int
ManagerID int
LocationID int

Now assume you want users of your application to be able to find an Employee by specifying any of the criteria they so desire. User1 may just want to find all employees who Joe Manager [ManagerID: 5] manages, User2 may want to find all employees who Joe Manager manages, but only if they are in the Technology Department [DepartmentID: 49]. Using the technique I shared with my co-worker would result in a proc that looks like this:

CREATE PROCEDURE ap_SearchEmployee
(
@EmployeeID int = null,
@DepartmentID int = null,
@ManagerID int = null,
@LocationID int = null
)

as

    SET NOCOUNT ON

    SELECT * FROM Employee e
        WHERE
        (@EmployeeID IS NULL OR e.EmployeeID = @EmployeeID)
        AND (@DepartmentID IS NULL OR e.DepartmentID = @DepartmentID)
        AND (@ManagerID IS NULL OR e.ManagerID = @ManagerID)
        AND (@LocationID IS NULL OR e.LocationID = @LocationID)

The where clause checks each parameter to see if it is NULL which means we don't want to filter our results by that parameter. If the parameter is not null it then checks the fields value with that of the parameter. By using this technique my co-worker was able to remove the duplicate logic and nasty if/else blocks within his stored procedure which provided him with a much cleaner solution!

# Darrell Norton's Blog

Friday, June 20, 2003 1:55 PM by    
Darrell Norton's Blog

# re: Optional Parameters in SQL Server Search Queries

Monday, August 25, 2003 7:59 AM by Jost Nickel    
That's in fact the coolest and most elegant solution for that problem I found on the web!

# re: Optional Parameters in SQL Server Search Queries

Wednesday, December 03, 2003 12:53 AM by Mike Pond    
This is just what I needed! Thanks!

# re: Optional Parameters in SQL Server Search Queries

Saturday, December 20, 2003 7:14 AM by Justin Bigelow    
Wow, very clean solution. Whenever I have to allow searches against a large number of fields I usually end up generating a where clause on the client side and pass it to a parameter of my sproc which then executes the dynamic sql.

# re: Optional Parameters in SQL Server Search Queries

Thursday, January 08, 2004 12:51 AM by Michal Levy    
There is even better way to do this if you are using MS SQL Server:

SELECT * FROM Employee e
WHERE
(e.EmployeeID = COALESCE(@EmployeeID,e.EmployeeID))
AND (e.DepartmentID = COALESCE(@DepartmentID,e.DepartmentID))
AND (e.ManagerID = COALESCE(@ManagerID, e.ManagerID))
AND .....

# re: Optional Parameters in SQL Server Search Queries

Saturday, March 06, 2004 9:47 AM by Jeffrey Peel    
I've found that COALESCE kills performance when querying large tables. It's usually better to code the IS NULL checks yourself as in the example in the article.

# re: Optional Parameters in SQL Server Search Queries

Tuesday, March 30, 2004 3:55 AM by Zombie Woof    
Woo Hoo! At the top of my Google search no less! A rare day indeed. Thanks.

ZW

# re: Optional Parameters in SQL Server Search Queries

Monday, April 05, 2004 11:13 PM by Andy    
WHERE
(e.EmployeeID = COALESCE(@EmployeeID, e.EmployeeID))
AND
(e.DepartmentID = COALESCE(@DepartmentID, e.DepartmentID))
AND
(e.ManagerID = COALESCE(@ManagerID, e.ManagerID))
AND
(e.LocationID = COALESCE(@LocationID, e.LocationID))

# re: Optional Parameters in SQL Server Search Queries

Monday, April 19, 2004 11:07 PM by Dave    
Nice!! I was just about to do the IF/ELSE business with dynamic SQL myself but this is exactly what I was looking for :)

# re: Optional Parameters in SQL Server Search Queries

Sunday, May 16, 2004 5:39 AM by Tim    
But what about indexing, especially if the table is large and you have several more criteria. If you create an index for each field, you still do a scan within the index for the other criteria, which can become a heavy operation (especially if you have a table with millions of records). If you index each possibility you end up with a factorial number of indexes (in the case of the example 4*3*2=24 possible indexes. Does anybody have a recommendation for how you index the table when any of N parameters can be NULL? I was thinking of possibly doing this:

SELECT
t1.*
FROM
(select * from employee WHERE (@employeeID IS NULL or employeeID = @employeeID) T1
INNER JOIN
(select employeeID from employee WHERE (@DepartmentID IS NULL or DepartmentID= @DepartmentID) T2 ON t1.employeeID = T2.employeeID
INNER JOIN
(select employeeID from employee WHERE (@ManagerID IS NULL or ManagerID = @ManagerID ) T3 ON t1.employeeID = t3.employeeID
INNER JOIN
(select employeeID from employee WHERE (@LocationID IS NULL or LocationID = @LocationID ) T4 ON t1.employeeID = T4.employeeID

This would allow me to have an index on each of the fields, and then the inner joins would get rid of those records that didn't meet the criteria. My question for anybody who may know is, is doing 4 individual joins where each table could return a large number of records less heavy than the query suggested above without 24 indexes (a factorial index would be huge for a large table, especially the indexes would be 4 column, 3 column, 2 columns, and 1 column).

# re: Optional Parameters in SQL Server Search Queries

Sunday, May 16, 2004 5:41 AM by Tim    
That should have been 24 columns in the indexes and not 24 indexes (4*3*2)

# re: Optional Parameters in SQL Server Search Queries

Sunday, May 16, 2004 2:27 PM by sudhir chawla (sudhirchawla@hotmail.com)    
Thanks! I was just about to do the IF/ELSE business with dynamic SQL myself, this is anothe good way but in case of large table i feel it compare for all parameter even most of them are null (can be bypass comparison via if condition)

# re: Optional Parameters in SQL Server Search Queries

Tuesday, May 18, 2004 7:55 PM by Wayne    
Hi all

This query is almost what I need but here's my problem.

I need to allow users of a website to search for customers based on CompanyName, FirstName, LastName, AccountNumber and TelephoneNumber fields. I also need this to be a LIKE query rather than an EQUALS query. Anyone got any ideas?


# re: Optional Parameters in SQL Server Search Queries

Tuesday, May 18, 2004 9:13 PM by Wayne    
Hi again

I've worked it out :)

(@Param IS NULL OR Param LIKE QUOTENAME('%' + @SearchStr + '%',''''))

Cheers


Wayne

# re: Optional Parameters in SQL Server Search Queries

Monday, May 24, 2004 9:59 PM by Doesnt Work    
I have tried the above but it doesnt produce any results-
because the the first thing to happen is that the parameters get made into nulls

# re: Optional Parameters in SQL Server Search Queries

Monday, May 24, 2004 10:28 PM by Steve    
I've had a good number of people use this method with very good success, so it does work. I'm sure the "first thing to happen is that the parameters get made into nulls" is referring to the declaration of the parameters for the procedure and the assignment of the default value for those parameters.

@EmployeeID int = null,
@DepartmentID int = null,
@ManagerID int = null,
@LocationID int = null

The above simply sets the default values for those parameters if they are not provided by the code calling the procedure.

# re: Optional Parameters in SQL Server Search Queries

Wednesday, June 02, 2004 1:32 AM by Scrod Burger    
Works great for Oracle too. Just change "@param_name" to ":param_name". Pretty sweet. Scord, scrod, scrod!!!

# re: Optional Parameters in SQL Server Search Queries

Thursday, June 03, 2004 7:11 AM by Cristina    
Hey

Works great for integers. How about nvarchars - I can't set them to NULL. What should I use instead?

Thanks

# re: Optional Parameters in SQL Server Search Queries

Thursday, June 03, 2004 8:36 PM by ofir    
when i try the (@Param IS NULL OR Param LIKE QUOTENAME('%' + @SearchStr + '%','''')) with varchar, what happends is that when the parameter is ""(nothing), it retrivies all the records, instead of retriving nothing, what is the solution for that?

# re: Optional Parameters in SQL Server Search Queries

Friday, June 04, 2004 1:06 AM by Cristina    
Ofir:

<a target="_new" href="http://weblogs.asp.net/rmclaws/archive/2004/02/18/75381.aspx">http://weblogs.asp.net/rmclaws/archive/2004/02/18/75381.aspx</a>

I tried it and it works.

# re: Optional Parameters in SQL Server Search Queries

Thursday, June 10, 2004 7:37 AM by Gaurav    
Very nice and relevant solution.
Id surely be using this.
Thanks

# re: Optional Parameters in SQL Server Search Queries

Wednesday, September 08, 2004 1:15 AM by Richard Lewis    
I found that using the Quotename method in conjunction with optional id column searches in the same query did not work. In order for a simple solution to be created, I passed in my varchar filter parameters either as blank strings or prepared with the '%' wildcard already. Id filters were passed in as normal, or with a value of -1 to signify no filtering on that parameter. A simple example is as follows (I've removed the select clause for business sensitivity:

CREATE PROCEDURE MyPROC
--If no search filter on id, pass in -1
--If there is an id in search filter, pass in that id
--If no search filter on varchar, pass in ''
--If there is text in search filter, pass in text between two % signs.
--e.g. with all filters used
--exec MyPROC 1, '%sel%', '%test%'

--e.g. with no id filter and one varchar filter used
--exec MyPROC -1, '', '%test%'


(
@AgencyId numeric(10,0) = -1,
@MediaName varchar(200) = '',
@JobTitle varchar(100) = ''
)
AS

SET NOCOUNT ON

--Do your own select here:
SELECT MY STUFF
FROM MY TABLE

--Now comes the magic bit:
WHERE
((@AgencyId = -1) OR (dbo.BZO_Agency.AgencyID = @AgencyId))
AND ((@MediaName = '') OR (dbo.BZO_Media.Title LIKE @MediaName))
AND ((@JobTitle = '') OR (dbo.BZO_Job.JobTitle LIKE @JobTitle))
GO

# re: Optional Parameters in SQL Server Search Queries

Wednesday, December 08, 2004 5:51 AM by ajc    
I have used this method for awhile, but am running into issues where the optimizer does not use the best method for approaching the query. If I give it non-NULL filter criteria, it often proceeds as if it did not have that extra helpful filtering information, and applies the filters later.

Hopefully I'll find a way around this.

# re: Optional Parameters in SQL Server Search Queries

Wednesday, December 15, 2004 11:18 PM by Ari    
Thanks a lot for the sql magic.

# re: Optional Parameters in SQL Server Search Queries

Wednesday, December 15, 2004 11:58 PM by dalf    
Thanks for a nice discussion.

# problem in sql query

Tuesday, December 21, 2004 6:42 PM by Hitesh    
I have one table in this table two fieldslike color and rate in color fields many type of records like G,H i want to if color =G then
rate &lt;3000 same as the all the color

# re: Optional Parameters when executing SQL Server Stored Procedure from ASP.NET

Sunday, April 03, 2005 8:09 PM by Ghosh Tuhin    
Please send me the answer

# re: Optional Parameters in SQL Server Search Queries

Wednesday, April 06, 2005 9:28 AM by Rhett    

I've used this and haven't found anything better. However I find that SQL Server isn't smart enough to correctly determine which index to use when searching the table. Say, column 1 has an index, but you pass in NULL for that parameter. SQL Server will still search that index, but since every row matches the null parameter, it just does a table scan.

I don't see why SQL Server can't be smarter, and determine the index strategy at run time and not compile time in this case.

# re: Optional Parameters in SQL Server Search Queries

Thursday, April 28, 2005 8:36 AM by Bill Vaughn    
Interesting discussion. However, I see a couple of issues: First, consider that when calling SPs the default value will not be taken unless the parameter is NOT passed by ADO in the Parameters collection. Passing a NULL is not the same...
Next, when SQL Server compiles the SP, it does so based on the first set of parameters it encounters. This query plan is cached and reused for all subsequent executions--regarless of how the parameters change. In some cases the query plan makes no sense at all based on the parameters provided subsequent to the initial execution.

hth

# re: Optional Parameters in SQL Server Search Queries

Monday, May 09, 2005 8:50 PM by Pete    
What is the best way to code the following, where a space indicates the email address is empty (not my DB):

Param - SQL

All - @email_addr_exists is null
No - Email_Address = ' '
Yes - Email_Address &lt;&gt; ' '

# re: Optional Parameters in SQL Server Search Queries

Wednesday, May 11, 2005 6:25 AM by samir    
HI there,
wonderful solution.
I wanted something like this for a proc that will return resluts based on parameters provided.
I still have one problem and that is of providing DateTime parameters as optional parameters.
Can any one suggest the correct method for providing datetime parameters as optional parameters ?
THanks

# re: Optional Parameters in SQL Server Search Queries

Thursday, May 19, 2005 8:02 AM by Martin Cron    
I fondly remember &quot;discovering&quot; this method of stored procedure filtering a while ago. It greatly reduces the complexity of my code base, while not sacrificing all of the benefits that stored procedures offer.

# re: Optional Parameters in SQL Server Search Queries

Tuesday, June 27, 2006 4:37 PM by Shawn Brock    
I'm sorry, but this doesn't work in 2000 or 2005 unless I'm doing something wrong.

Suppose I have a table:
MyTable
--MyID int
--MyName varchar(200)

SELECT *
FROM MyTable
WHERE (MyID = @MyID or @MyID IS NULL)

will not do an index seek, whereas dynamic sql that generates:

SELECT *
FROM MyTable
WHERE MyID = @MyID

will.

If I'm mistaken, I'd love to see some STATISTICS IO proving me wrong.

# re: Optional Parameters in SQL Server Search Queries

Tuesday, June 27, 2006 5:53 PM by Steve    
By "it doesn't work" do you mean it's not the most optimal solution?

Keep in mind that the solution given was specific to having optional parameters in stored procedures with the assumption that dynamic SQL was not an option. Obviously if you could generate dynamic SQL the solution provided isn't very relevant.

# re: Optional Parameters in SQL Server Search Queries

Thursday, September 14, 2006 9:51 PM by Juan    
This renders abhorrent performance.

See the following article for the full explanation:
http://www.sommarskog.se/dyn-search.html

# re: Optional Parameters in SQL Server Search Queries

Wednesday, January 03, 2007 5:54 AM by Gary Woodfine    
You just shortened my critical path in my application , thanks!!

# re: Optional Parameters in SQL Server Search Queries

Monday, March 05, 2007 8:09 PM by Jim Damato    
Wow. Simple, so simple. I wish my development team had found this before they made all that Dynamic SQL for our search queries. Hopefully Microsoft won't change the order of evaluation so this continues to work.

# re: Optional Parameters in SQL Server Search Queries

Wednesday, March 07, 2007 9:08 AM by KJ    
How do you go about using this procedure when you have optional parameters that must search data from two tables?

# re: Optional Parameters in SQL Server Search Queries

Tuesday, June 05, 2007 10:24 AM by Nosmo King    
Nice one bro!

# re: Optional Parameters in SQL Server Search Queries

Friday, June 08, 2007 10:26 AM by Saqlain Abbas    
I will just say gr8.

Post a Comment

 
 
Prove you're not a spammer: 
6 + 5 =