Single SP for Multiple Search Scenario

Introduction

Sometimes, we need to write multiple Stored Procedures with same tables because of different filter option. Here, we'll try to write down 1 stored procedure which we can use in different places with different filter.

Background

Suppose we've 2 tables, PRODUCTS and CATEGORIES. PRODUCTS have ProductID, CategoryID, ProductName, IsActive column and CATEGORIES have CategoryID, CategoryName, IsActive fields.

Now, we want to write a SP to filter data from PRODUCTS and CATEGORIES with every field which we can use in a different scenario. 

Using the Code

The SP should be written in the following way:

CREATE PROCEDURE [dbo].[YOUR_PROCEDURE_NAME] 
@ProductID INT = NULL
,@CategoryID INT = NULL
,@ProductName VARCHAR(200) = NULL
,@CategoryName VARCHAR(200) = NULL
,@IsActive BIT = NULL

Declare all parameters which you want to use with default value NULL. So if parameter is not passed in SP, then it will take value NULL.

AS      
BEGIN 
    SELECT PRO.*, CAT.* FROM 
	PRODUCTS PRO
	INNER JOIN CATEGORIES CAT ON PRO.CategoryID = CAT.CategoryID
	WHERE PRO.ProductID = CASE WHEN @ProductID IS NULL THEN PRO.ProductID ELSE @ProductID END
	AND WHERE CAT.CategoryID = CASE WHEN @CategoryID IS NULL THEN CAT.CategoryID _
	ELSE @CategoryID END
	AND WHERE CAT.CategoryID = CASE WHEN @CategoryID IS NULL THEN CAT.CategoryID _
	ELSE @CategoryID END
	AND WHERE PRO.ProductName LIKE CASE WHEN @ProductName IS NULL _
	THEN PRO.ProductName ELSE '%' + @ProductName + '%' END
	AND WHERE CAT.CategoryName LIKE CASE WHEN @CategoryName IS NULL _
	THEN CAT.CategoryName ELSE '%' + @CategoryName + '%' END
	AND WHERE PRO.IsActive = CASE WHEN @IsActive IS NULL THEN PRO.IsActive ELSE @IsActive END
	AND WHERE CAT.IsActive = CASE WHEN @IsActive IS NULL THEN CAT.IsActive ELSE @IsActive END
END

If parameter has value, then it will filter data from table for that particular field, but if parameter has NULL value, then it gives all data for that particular field.

So now, if you exec the sp in the following way:

EXEC YOUR_PROCEDURE_NAME

or:

EXEC YOUR_PROCEDURE_NAME NULL, NULL, NULL, NULL, NULL

It will give you all data from both tables. If you want to filter all active Products which have "Large" in Product Name, then you need to execute the following query:

EXEC YOUR_PROCEDURE_NAME NULL, NULL, 'Large', NULL, 1