SQL with Manoj

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.