Optional logical OR parameters for SQL Query

Question

I have a SQL Server 2008 table with the following structure and data

INSERT INTO [ProductDetails]([ProductID],[ProductName],[Vendor],[Category])
VALUES(1,'Milk','V1','Food')

INSERT INTO [ProductDetails]([ProductID],[ProductName],[Vendor],[Category])
VALUES(2,'Milk','V2','Food')

INSERT INTO [ProductDetails]([ProductID],[ProductName],[Vendor],[Category])
VALUES(3,'Banana','V2','Food')

I want to apply logical OR in my search criteria with the following search parameters

DECLARE @ProductName VARCHAR(10)
DECLARE @Vendor VARCHAR(10)

The expected results are listed below in various scenarios.

---CASE 1
SET @ProductName = NULL
SET @Vendor = NULL
--Expected ProductID = 1,2,3

---CASE 2
SET @ProductName = 'Milk'
SET @Vendor = 'V2'
--Expected ProductID = 1,2,3

---CASE 3
SET @ProductName = NULL
SET @Vendor = 'V2'
--Expected ProductID = 2,3

---CASE 3
SET @ProductName = 'Banana'
SET @Vendor = NULL
--Expected ProductID = 3

---CASE 5
SET @ProductName = 'Banana'
SET @Vendor = 'V2'
--Expected ProductID = 2,3

The only solution, I can think of now, is to run separate queries as follows.

1. IF both @ProductName and @Vendor are NULL, select all the products

2. IF @ProductName and @Vendor are non-null, make a union of two select queries (query based on @ProductName value and query based on @Vendor value)

3. IF @ProductName is NOT null and @Vendor is NULL, select query result based on @ProductName value

4. IF @ProductName is null and @Vendor is NOT null, select query result based on @Vendor value.

Is there a better solution that can be done in one query like the one we do for logical AND?

REFERENCE:

1. Implementing search functionality with multiple optional parameters against database table

Answer 1

This is trickier than the standard method. In that, you would take the intersection when both are defined:

where (@ProductName = ProductName) or
      (@Vendor = VendorName) or
      (@ProductName is null and @Vendor is null)

UPDATE by OP

The idea is:

check whether any of the records in the table satisfy criteria in line 1

check whether any of the records in the table satisfy criteria in line 2

....

....

check whether all inputs are null (i.e, search is made without any input)

Note: If all the input parameters are NULL, all the records in the table will be returned in the result.

Why?

Because, all the records in the table will meet this criteria. (Data need not meet any criteria. There is no comparing about data - it's about parameters). So if the condition inside the last line parentheses are met, all the records are happy to be appeared in the result.

Answer 2

You can do this:

where (@ProductName IS NULL AND @Vendor IS NULL)
   OR (@ProductName IS NOT NULL AND ProductName=@ProductName)
   OR (@Vendor IS NOT NULL AND Vendor=@Vendor)