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

Creating Stored Procedures with Dynamic Search (filter)


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]
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

 

–> 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.
 


Advertisements
Categories: Stored Procedures Tags:
  1. 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?

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

    Very nice example

  3. October 25, 2014 at 2:40 am

    yes its working fine nice post. Thanks

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

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

  5. February 6, 2013 at 11:46 pm

    This was a huge help. Thanks!

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

    Excellent thank you so much

  7. 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.

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

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

    Thanks
    Vidhya

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

    Good idea

  10. 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.

  11. 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

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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: