7

Creating Stored Procedures with Dynamic Search (filter) | SQL with Manoj

 3 years ago
source link: https://sqlwithmanoj.com/2011/12/30/creating-stored-procedures-with-dynamic-search-filter/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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.

Related


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK