CODEWITHSUNDEEP
sqlsql-servervb.net
gwc
gwc

Reputation: 171

Multiple Parameter Search in SQL Server 2000

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 search on all/some/one of the fields. If user only enters data on say first name and home phone number what would I need to do for the rest of the parameters where data was not entered. I tried the select statement below but it 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'

Upvotes: 2

Views: 1022

Answers (6)

Nathan Griffiths
Nathan Griffiths

Reputation: 12766

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.

Upvotes: 0

Russ Cam
Russ Cam

Reputation: 125538

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.

Upvotes: 0

GregD
GregD

Reputation: 7000

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.

Upvotes: 0

KenJ
KenJ

Reputation: 306

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

Upvotes: 0

Jason Kester
Jason Kester

Reputation: 6031

I use this pattern a lot:

Select Last_Name, First_Name, Mid_Name, DOB, Home_Phone, Work_Phone from dbo.tblClient
Where (@LastName is null or Last_Name Like '%'+ @LastName)
and (@FirstName is null or First_Name Like '%'+ @FirstName)
and (@HomePhone is null or Home_Phone Like '%'+ @HomePhone)
-- etc...

It will ignore anything that's not supplied, while still giving good performance. Better still, it doesn't resort to dynamic SQL to pull it off.

Upvotes: 3

AnthonyWJones
AnthonyWJones

Reputation: 189535

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.

Upvotes: 0

Related Questions