Multiple Parameter Search in SQL Server 2000
I have a search screen in a Visual Basic .Net App that has text boxes for:
- First Name Varchar(50)
- Last Name Varchar(50)
- Middle Name Varchar(50)
- DOB DateTime
- Home Phone Varchar(10)
- 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'
6 Solutions collect form web for “Multiple Parameter Search in SQL Server 2000”
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.
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.
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
Erland Sommarskog has some great articles on different ways to do this and their performance implications here
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.
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.
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.