Multiple Parameter Search in SQL Server 2000

Question

I have a search screen in a Visual Basic .Net App that has text boxes for:

1. First Name Varchar(50)
2. Last Name Varchar(50)
3. Middle Name Varchar(50)
4. DOB DateTime
5. Home Phone Varchar(10)
6. Work Phone Varchar(10)

How would I create a stored procedure in SQL Server 2000 that would allow me to be able to searh on all/some/one of the fields. If user only enters data on say first name and home phone number what would you need to do for the rest of the paramaters where data was not entered. I tried the select statement below but doesn't work properly.

    Select Last_Name, First_Name, Mid_Name, DOB, Home_Phone, Work_Phone from dbo.tblClient
Where Last_Name Like '%@LastName' and
    First_Name Like '%@FirstName' and
    Mid_Name Like '%@MiddleName' and
    DOB Like '%DOB' and
    Home_Phone Like '%@HomePhone' and
    Work_Phone Like '%@WorkPhone'

Answer 1

The quick way to do this would be something like

Where (Last_Name Like @LastName + '%' OR @LastName IS NULL) and (First_Name Like @FirstName + '%' OR @FirstName IS NULL) and etc...

Erland Sommarskog has some great articles on different ways to do this and their performance implications here

Answer 2

In your stored procedure or in your VB, you're going to have to decide how you want to handle no input. For example I use:

IF ltrim(rtrim(@FirstName)) = ''
SET @FirstName = null

...in my stored procedure. You may have to experiment with ORs instead of ANDs. You're basically telling your query that you have to meet all of those conditions by using AND, regardless of there not being any input to go by.

Answer 3

A quick an dirty solution.

Select Last_Name, First_Name, Mid_Name, DOB, Home_Phone, Work_Phone from dbo.tblClient
Where (Last_Name Like '%' + @LastName OR @LastName Is Null) and
(First_Name Like '%' + @FirstName OR @FirstName Is Null) and
(Mid_Name Like '%' + @MiddleName OR @MiddleName Is Null) and
(DOB Like '%' + @DOB OR @DOB Is Null) and
(Home_Phone Like '%' + @HomePhone OR @HomePhone Is Null and
(Work_Phone Like '%' + @WorkPhone OR @WorkPhone Is Null)

Note I've correct the parameter usage. Wouldn't you also want a wildcard on the other side of the parameter as well? Also would you really use a Like to a Date of Birth field?

This isn't going to perform very well on a large table. A far more performant solution would be to construct the SQL with only the required fields in the Where clause.

Answer 4

Just to clarify why your original SQL doesn't work;

You need to concatenate the % wildcard to the parameter value, but what you have written is creating a literal string that contains the wildcard and the name of the parameter e.g.

Work_Phone Like '%@WorkPhone'

should be written as

Work_Phone Like '%' + @WorkPhone

The SQL you have should work if you are returning an empty string for parameters that don't have a value entered (i.e. you are returning "", and not NULL) - the Like comparison for those fields will then only contain the % wildcard (i.e. match any value). However, this is not very efficient as ideally you only want to do a comparison on the fields that the user has entered a value. This would probably require some dynamically generated SQL , as shown in the article linked to by kenj.

If your tblClient table is not that large though, then what you have done might be sufficient.

Answer 5

How about using ISNULL() function to determine if a value has been passed into the stored procedure and if not, setting the WHERE clause field value to search for equal to itself in each case (if someone can think of a better choice of words for describing this, I will update the answer).

 SELECT 
     Last_Name, 
     First_Name, 
     Mid_Name, 
     DOB, 
     Home_Phone, 
     Work_Phone 
 FROM 
     dbo.tblClient
 WHERE 
    Last_Name LIKE '%' + ISNULL(@LastName, Last_Name) AND
    First_Name LIKE '%' + ISNULL(@FirstName, First_Name) AND
    Mid_Name LIKE '%' ISNULL(@MiddleName, Mid_Name) AND
    DOB LIKE '%' + ISNULL(@DOB, DOB) AND
    Home_Phone LIKE '%' + ISNULL(@HomePhone, Home_Phone) AND
    Work_Phone LIKE '%' + ISNULL(@WorkPhone, Work_Phone)

You could also set a NULL default value for each of your stored procedure parameters.