Execute the following Microsoft SQL Server T-SQL script in Query Editor to demonstrate the design for missing filter parameter(s). In case of all 3 parameters missing, the entire JOINed Production.Product table content is returned without the WHERE filter.

USE AdventureWorks2008;

GO

CREATE PROCEDURE sprocProductFilter

                @LowPrice    SMALLMONEY  = NULL,

                @SubCategory VARCHAR(32)  = NULL,

                @Color       VARCHAR(16)  = NULL

AS

  SET nocount  ON;

  SELECT p.*

  FROM   Production.Product p

         INNER JOIN Production.ProductSubcategory sc

           ON p.ProductSubcategoryID = sc.ProductSubcategoryID

  WHERE  1 = 1

         AND (@LowPrice <= ListPrice

               OR @LowPrice IS NULL)

         AND (@SubCategory = sc.Name

               OR @SubCategory IS NULL)

         AND (@Color = Color

               OR @Color IS NULL)

GO

exec sprocProductFilter 700.0, 'Road Frames', 'Red'

go

exec sprocProductFilter NULL, 'Road Frames', 'Red'

go

exec sprocProductFilter NULL, NULL, 'Red'

go

exec sprocProductFilter 700.0, 'Road Frames'

go

exec sprocProductFilter 700.0

go

exec sprocProductFilter

go

Related article:

SQL Stored Procedures