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.

  • Is there a Profiler equivalent for MySql?
  • Using SQL script through code
  • Update cached data
  • CSV Import with Validation
  • Rounding-up TSQL
  • Query access database from SQL management studio without using linked servers
  •     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).

        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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.