How do I create a stored procedure that will optionally search columns?

Question

I'm working on an application for work that is going to query our employee database. The end users want the ability to search based on the standard name/department criteria, but they also want the flexibility to query for all people with the first name of "James" that works in the Health Department. The one thing I want to avoid is to simply have the stored procedure take a list of parameters and generate a SQL statement to execute, since that would open doors to SQL injection at an internal level.

Can this be done?

Answer 1

While the COALESCE trick is neat, my preferred method is:

CREATE PROCEDURE ps_Customers_SELECT_NameCityCountry
    @Cus_Name varchar(30) = NULL
    ,@Cus_City varchar(30) = NULL
    ,@Cus_Country varchar(30) = NULL
    ,@Dept_ID int = NULL
    ,@Dept_ID_partial varchar(10) = NULL
AS
SELECT Cus_Name
       ,Cus_City
       ,Cus_Country
       ,Dept_ID
FROM Customers
WHERE (@Cus_Name IS NULL OR Cus_Name LIKE '%' + @Cus_Name + '%')
      AND (@Cus_City IS NULL OR Cus_City LIKE '%' + @Cus_City + '%')
      AND (@Cus_Country IS NULL OR Cus_Country LIKE '%' + @Cus_Country + '%')
      AND (@Dept_ID IS NULL OR Dept_ID = @DeptID)
      AND (@Dept_ID_partial IS NULL OR CONVERT(varchar, Dept_ID) LIKE '%' + @Dept_ID_partial + '%')

These kind of SPs can easily be code generated (and re-generated for table-changes).

You have a few options for handling numbers - depending if you want exact semantics or search semantics.

Answer 2

The most efficient way to implement this type of search is with a stored procedure. The statement shown here creates a procedure that accepts the required parameters. When a parameter value is not supplied it is set to NULL.

CREATE PROCEDURE ps_Customers_SELECT_NameCityCountry
@Cus_Name varchar(30) = NULL,
@Cus_City varchar(30) = NULL,
@Cus_Country varchar(30) =NULL
AS
SELECT Cus_Name,
       Cus_City,
       Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
      Cus_City = COALESCE(@Cus_City,Cus_City) AND
      Cus_Country = COALESCE(@Cus_Country,Cus_Country)

Taken from this page: http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

I've done it before. It works well.

Answer 3

Erland Sommarskog's article Dynamic Search Conditions in T-SQL is a good reference on how to do this. Erland presents a number of strategies on how to do this without using dynamic SQL (just plain IF blocks, OR, COALESCE, etc) and even lists out the performance characteristics of each technique.

In case you have to bite the bullet and go through the Dynamic SQL path, you should also read Erland's Curse and Blessings of Dynamic SQL where he gives out some tips on how to properly write dynamic SQLs

Answer 4

It can be done, but usually these kitchen-sink procedures result in some poor query plans.

Having said all that, here is the tactic most commonly used for "optional" parameters. The normal approach is to treat NULL as "ommitted".

SELECT
  E.EmployeeID,
  E.LastName,
  E.FirstName
WHERE
  E.FirstName = COALESCE(@FirstName, E.FirstName) AND
  E.LastName = COALESCE(@LastName, E.LastName) AND
  E.DepartmentID = COALESCE(@DepartmentID, E.DepartmentID)

EDIT: A far better approach would be parameterized queries. Here is a blog post from one of the world's foremost authorities in this domain, Frans Bouma from LLBLGen Pro fame:

Stored Procedures vs. Dynamic Queries

Answer 5

Using the COALESCE method has a problem in that if your column has a NULL value, passing in a NULL search condition (meaning ignore the search condition) will not return the row in many databases.

For example, try the following code on SQL Server 2000:

CREATE TABLE dbo.Test_Coalesce (
    my_id   INT NOT NULL IDENTITY,
    my_string   VARCHAR(20) NULL )
GO
INSERT INTO dbo.Test_Coalesce (my_string) VALUES (NULL)
INSERT INTO dbo.Test_Coalesce (my_string) VALUES ('t')
INSERT INTO dbo.Test_Coalesce (my_string) VALUES ('x')
INSERT INTO dbo.Test_Coalesce (my_string) VALUES (NULL)
GO
DECLARE @my_string  VARCHAR(20)
SET @my_string = NULL
SELECT * FROM dbo.Test_Coalesce WHERE my_string = COALESCE(@my_string, my_string)
GO

You will only get back two rows because in the rows where the column my_string is NULL you are effective getting:

my_string = COALESCE(@my_string, my_string) =>
my_string = COALESCE(NULL, my_string) =>
my_string = my_string =>
NULL = NULL

But of course, NULL does not equal NULL.

I try to stick with:

SELECT
     my_id,
     my_string
FROM
     dbo.Test_Coalesce
WHERE
     (@my_string IS NULL OR my_string = @my_string)

Of course, you can adjust that to use wild cards or whatever else you want to do.

Answer 6

Copying this from my blog post:

USE [AdventureWorks]
GO

CREATE PROCEDURE USP_GET_Contacts_DynSearch
(
    -- Optional Filters for Dynamic Search
    @ContactID          INT = NULL, 
    @FirstName          NVARCHAR(50) = NULL, 
    @LastName           NVARCHAR(50) = NULL, 
    @EmailAddress       NVARCHAR(50) = NULL, 
    @EmailPromotion     INT = NULL, 
    @Phone              NVARCHAR(25) = NULL
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE
        @lContactID         INT, 
        @lFirstName         NVARCHAR(50), 
        @lLastName          NVARCHAR(50), 
        @lEmailAddress      NVARCHAR(50), 
        @lEmailPromotion    INT, 
        @lPhone             NVARCHAR(25)

    SET @lContactID         = @ContactID
    SET @lFirstName         = LTRIM(RTRIM(@FirstName))
    SET @lLastName          = LTRIM(RTRIM(@LastName))
    SET @lEmailAddress      = LTRIM(RTRIM(@EmailAddress))
    SET @lEmailPromotion    = @EmailPromotion
    SET @lPhone             = LTRIM(RTRIM(@Phone))

    SELECT
        ContactID, 
        Title, 
        FirstName, 
        MiddleName, 
        LastName, 
        Suffix, 
        EmailAddress, 
        EmailPromotion, 
        Phone
    FROM [Person].[Contact]
    WHERE
        (@lContactID IS NULL OR ContactID = @lContactID)
    AND (@lFirstName IS NULL OR FirstName LIKE '%' + @lFirstName + '%')
    AND (@lLastName IS NULL OR LastName LIKE '%' + @lLastName + '%')
    AND (@lEmailAddress IS NULL OR EmailAddress LIKE '%' + @lEmailAddress + '%')
    AND (@lEmailPromotion IS NULL OR EmailPromotion = @lEmailPromotion)
    AND (@lPhone IS NULL OR Phone = @lPhone)
    ORDER BY ContactID

END
GO

Answer 7

We can use Generic @Search Parameter and pass any value to it for searching.

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: --
-- Create date:
-- Description: --
-- =============================================
CREATE PROCEDURE [dbo].[usp_StudentList]
    @PageNumber INT    = 1, -- Paging parameter
    @PageSize   INT    = 10,-- Paging parameter
    @Search  VARCHAR(MAX) = NULL, --Generic Search Parameter
    @OrderBy VARCHAR(MAX) = 'FirstName', --Default Column Name 'FirstName' for records ordering
    @SortDir VARCHAR(MAX) = 'asc' --Default ordering 'asc' for records ordering
AS
BEGIN
    SET NOCOUNT ON;

    --Query required for paging, this query used to show total records
    SELECT COUNT(StudentId) AS RecordsTotal FROM Student

    SELECT Student.*, 
        --Query required for paging, this query used to show total records filtered
        COUNT(StudentId) OVER (PARTITION BY 1) AS RecordsFiltered 
    FROM Student
    WHERE 
    --Generic Search 
    -- Below is the column list to add in Generic Serach
    (@Search IS NULL OR Student.FirstName LIKE '%'+ @Search +'%')
    OR (@Search IS NULL OR Student.LastName LIKE '%'+ @Search +'%')
    --Order BY
    -- Below is the column list to allow sorting
    ORDER BY 
    CASE WHEN @SortDir = 'asc' AND @OrderBy = 'FirstName' THEN Student.FirstName END,
    CASE WHEN @SortDir = 'desc' AND @OrderBy = 'FirstName' THEN Student.FirstName  END DESC,
    CASE WHEN @SortDir = 'asc' AND @OrderBy = 'LastName' THEN Student.LastName END,
    CASE WHEN @SortDir = 'desc' AND @OrderBy = 'LastName' THEN Student.LastName  END DESC,
    OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;
END

Answer 8

My first thought was to write a query something like this...

SELECT EmpId, NameLast, NameMiddle, NameFirst, DepartmentName
  FROM dbo.Employee
       INNER JOIN dbo.Department ON dbo.Employee.DeptId = dbo.Department.Id
 WHERE IdCrq IS NOT NULL
       AND
       (
          @bitSearchFirstName = 0
          OR
          Employee.NameFirst = @vchFirstName
       )
       AND
       (
          @bitSearchMiddleName = 0
          OR
          Employee.NameMiddle = @vchMiddleName
       )
       AND
       (
          @bitSearchFirstName = 0
          OR
          Employee.NameLast = @vchLastName
       )
       AND
       (
          @bitSearchDepartment = 0
          OR
          Department.Id = @intDeptID
       )

...which would then have the caller provide a bit flag if they want to search a particular field and then supply the value if they are to search for it, but I don't know if this is creating a sloppy WHERE clause or if I can get away with a CASE statement in the WHERE clause.

As you can see this particular code is in T-SQL, but I'll gladly look at some PL-SQL / MySQL code as well and adapt accordingly.

Answer 9

I would stick with the NULL/COALESCE method over AdHoc Queries, and then test to make sure you don't have performance problems.

If it turns out that you have slow running queries because it's doing a table scan when you're searching on columns that are indexed, you could always supplement the generic search stored procedure with additional specific ones that allow searching on these indexed fields. For instance, you could have a special SP that does searches by CustomerID, or Last/First Name.

Answer 10

Write a procedure to insert all employee data whose name start with A in table??