Passing multiple NULL and Value to Where clause in SQL Server

Last Reply 4 months ago By dharmendr

Posted 4 months ago

hello,

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 4 months ago Modified on 4 months 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.

Database

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

Download Northwind Database

SQL

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)