In some scenario we have to filter with parameter base or show
all record if no filter parameter is selected. So most of the developer I have seen write the
conditional base query to check whether parameter is selected or not accordingly
write the queries.
But here you can just simply writer single query with some
trick in parameter by passing only NULL value to parameter you get all records
and on filter you get respective record.
--Table Script:
CREATE TABLE [dbo].[ProductMaster](
[productID]
[int] IDENTITY(1,1) NOT NULL,
[productName]
[varchar](100) NULL,
[price]
[numeric](18, 2) NULL,
[quantity]
[int] NULL,
[productCategoryID]
[int] NULL,
CONSTRAINT [PK_ProductMaster] PRIMARY KEY CLUSTERED
(
[productID]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--Insert values into table:
INSERT INTO
ProductMaster(productName, price, quantity, productCategoryID)
VALUES
('Mobile', 35000, 5, 1 ),
('Hard disk', 2100, 6, 2 ),
('Camera', 5000, 10, 3 ),
('IPad', 35000, 2, 4 ),
('EarPhone', 250,
11, 5 )
GO
--Declare Category ID to filter
/*
To
get product(s) on category basis pass Category Id i.e. 1,2,3 etc
To
get all products pass NULL
*/
DECLARE @productCategoryID as int=NULL --2
SELECT
productID
,productName
,price
,quantity
FROM dbo.ProductMaster
WHERE (productCategoryID = @productCategoryID OR
@productCategoryID IS NULL)
I hope it will help you to filter on parameter basis or all
record by passing only NULL value.
Cheers,
ved pathak
Comments