Home > Stored Procedures > Creating Stored Procedures with Dynamic Search (filter)

Creating Stored Procedures with Dynamic Search (filter)

December 30, 2011 Leave a comment Go to comments

Stored Procedures are a set of single or group of Transact-SQL statements with or without control flow and other business logic to query database tables and get us our desired results. They also assist us in achieving a consistent implementation of logic across applications and also improve performance. I will discuss the benefits on these in another separate post.

Here we will see how can we create Stored Procedures with Dynamic search capabilities.

By Dynamic search capability I mean whatever combination of input provided to the Stored Procedure as parameters it would filter and fetch the expected records for us.

–> Let’s check with a sample code using [AdventureWorks] Database and [Person].[Contact] Table:

USE [AdventureWorks]

	-- 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
		@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))

	FROM [Person].[Contact]
		(@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)


–> OK, now let’s test this SP:

-- No parameters provided, it will fetch all records:
EXEC USP_GET_Contacts_DynSearch -- 19972 records

-- On providing only FirstName param:
EXEC USP_GET_Contacts_DynSearch @FirstName = 'john' -- 104 records

-- On providing a combination of FirstName & LastName params:
EXEC USP_GET_Contacts_DynSearch @FirstName = 'john', @LastName = 'c' -- 19 records

-- On providing a combination of FirstName, LastName & EmailAddress params:
EXEC USP_GET_Contacts_DynSearch @FirstName = 'john', @LastName = 'c', @EmailAddress = '3' -- 6 records

-- On providing only EmailAddress param:
EXEC USP_GET_Contacts_DynSearch @EmailAddress = 'samuel' -- 73 records

In the above exercise we can see that we can filter or records based upon different or a combination of different parameters and get our desired results.

Please Note: In WHERE clause condition you can use equal-to operator or the LIKE, or BETWEEN depending upon your requirements.

>> Check & Subscribe my [YouTube videos] on SQL Server.

Categories: Stored Procedures Tags:
  1. udhaya
    January 4, 2012 at 6:10 am

    I have used dynamic query with exec for searching scenario for long time. the logic you are handling is really super and I am very thank full for you for sharing this.
    good luck

  2. veer
    February 15, 2012 at 2:34 pm

    Hi Sir … Above example is awesome but i want to search between two dates. So, how can i use it using above example.

  3. Rajendra
    April 11, 2012 at 3:50 pm

    Good idea

  4. Vidhya
    June 6, 2012 at 11:28 am

    Great one Manoj. This post really helped me fine tuning my SP. Hats off to you.


  5. June 14, 2012 at 1:16 pm

    I’d recommend reading Erland Sommarskog’s articles on Dynamic Search (http://www.sommarskog.se/dyn-search.html) to see the alternatives that exist to reduce the performance problems associated with the solution presented here. Often times, Dynamic SQL with certificate signed procedures provide better performance without any additional risk security wise.

  6. suat
    December 26, 2012 at 2:55 am

    Excellent thank you so much

  7. February 6, 2013 at 11:46 pm

    This was a huge help. Thanks!

  8. Vishal patel
    March 22, 2013 at 12:54 pm

    Excellent thank you so much……………
    nicely worked for filter product by FK

  9. October 25, 2014 at 2:40 am

    yes its working fine nice post. Thanks

  10. kishore
    December 5, 2014 at 3:23 pm

    Very nice example

  11. May 14, 2015 at 4:10 pm

    what about in below condition in above procedure.
    select * from ST_Client where FirstName is null or FirstName like ‘%”%’

    we have an other approach to write a dynamic query. Which one is better?

  12. ravi
    February 10, 2019 at 9:41 pm

    very nice example if we replace the ”AND” logical operator with “or” logical operator it will not matching the records as per input values.so, it will happens when we give the input parameters with some values and some parameters with empty string

  1. December 30, 2011 at 10:07 am
  2. June 20, 2016 at 6:39 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: