Passing multiple NULL and Value to Where clause in SQL Server

Last Reply one year ago By dharmendr

Posted one year ago


i have this below query i want to apply filters on 

categoryid, brandid, productid, subcateogryid

select categoryid, brandid, productid, subcategory From Product

all are integes if any filter is not pass all data should come

if categoryid is pass so all data matching categoryid should come if categoryid and brandid is passed so all data matching cateogryid and brandid should come and go on

storeprocedure would work pls advice

Posted one year ago Modified on one year ago

Hi nauna,

For this you have to check the input parameter with IS NULL condition.

Check this sample query and correct your query based on your table structure.


For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database


DECLARE @OrderId INT, @EmployeeId INT, @ShipVia INT
SET @OrderId = NULL
SET @EmployeeId = 6
SET @ShipVia = 1

SELECT OrderID,EmployeeID,ShipVia,Freight,ShipCity FROM Orders
WHERE (OrderID = @OrderId OR @OrderId IS NULL)
AND (EmployeeID = @EmployeeId OR @EmployeeId IS NULL)
AND (ShipVia = @ShipVia OR @ShipVia IS NULL)