How to create Stored Procedures with Filters – Dynamic Search in MS SQL Server Instance

When we were in need to upgrade one of our client’s application code, we decided to filter the data in the database instead of through our application code. 

The logic behind the idea is that the database engine will do a better job at finding the most efficient way of completing the task than the code could. Especially when it comes to things like making the results conditional on operations performed on the data. 

In my opinion, there are certain things that SQL is made to do:

    • joins — when use code, it would require complex array manipulation
    • filtering data (where) — when use code, it would require heavy inserting and deleting of items in lists
    • selecting columns — when use code, it would require heavy list or array manipulation
    • aggregate functions — when use code, it would require arrays to hold values and complex switch cases
    • foreign key /primary key— when use code, it would require queries prior to insert and assumes nobody will use the data outside app

Delegating much of the heavy lifting to SQL Server and reducing the amount of IO as much as possible, will eliminate writing tons of code with no added value, and it definitely means less code to debug and maintain. 

Here is an example on how we can create a Stored Procedures with Dynamic search capabilities:

CREATE PROCEDURE [dbo].[GET_Contacts_Filter_SP]
(
    -- Optional Filters for Filter - Dynamic Search
   @ContactID          INT = NULL
   @FirstName          NVARCHAR(50) = NULL
   @LastName           NVARCHAR(50) = NULL
   @EmailAddress       NVARCHAR(50) = NULL
   @EmailPromotion     INT = NULL
   @Title              NVARCHAR(8) = NULL
)
AS
BEGIN
   SET NOCOUNT ON
   DECLARE
       @fContactID         INT
       @fFirstName         NVARCHAR(50), 
       @fLastName          NVARCHAR(50), 
       @fEmailAddress      NVARCHAR(50), 
       @fEmailPromotion    INT
       @fTitle             NVARCHAR(8)
   SET @fContactID         = @ContactID
   SET @fFirstName         = LTRIM(RTRIM(@FirstName))
   SET @fLastName          = LTRIM(RTRIM(@LastName))
   SET @fEmailAddress      = LTRIM(RTRIM(@EmailAddress))
   SET @fEmailPromotion    = @EmailPromotion
   SET @fTitle             = LTRIM(RTRIM(@Title))

   
SELECT

     ContactID,
     PersonType,
     Title,
     FirstName,
     LastName,
     EmailAddress,
     EmailPromotion
   FROM [Person]
   -- Optional Filters for Filter - Dynamic Search
   WHERE
       (@fContactID IS NULL OR ContactID = @fContactID)
   AND (@fFirstName IS NULL OR FirstName LIKE '%' + @fFirstName + '%')
   AND (@fLastName IS NULL OR LastName LIKE '%' + @fLastName + '%')
   AND (@fEmailAddress IS NULL OR EmailAddress LIKE '%' + @fEmailAddress + '%')
   AND (@fEmailPromotion IS NULL OR EmailPromotion = @fEmailPromotion)
   AND (@fTitle IS NULL OR Title LIKE '%' + @fTitle + '%')
   ORDER BY ContactID
END
GO

Here is how [Person] table looks like:

Filtering with Stored Procedures

Here are some test runs for  the SP:

Stored Procedures with Filters 

Dynamic Search with Stored Procedures

Dynamic Search with SP

Interested in optimizing your database for your Business?  Anyon Consulting can help.  Contact us today to learn more about our performance analytics and improvement services. 

Scroll to top