Hi anoopsingh,
In below code as you are passing maxprice and minprice as 0 so definitely you won’t get any records, if there is maxprice or minprice 0 in Database then it will show records
sp_Product_GetProductByPriceStateCityArea 'Mobile Phones',0,0,'','',''
Here I have written script that full-fill your requirement.
I hope this will help you out.
SQL
DECLARE @temp AS TABLE(SubCategory NVARCHAR(50),Price NUMERIC(18,2),MinPrice NUMERIC(18,2),MaxPrice NUMERIC(18,2),[State] NVARCHAR(50),City NVARCHAR(50),Area NVARCHAR(50))
INSERT INTO @temp VALUES
('Cloths',152.59,100.00,500.00,'Maharashtra','Mumbai','Goregoan')
,('Mobiles',1568.89,1000.99,5000.00,'Maharashtra','Pune','Pimpri')
,('Electonics',50000.55,100.52,100000.25,'Maharashtra','NaviMumbai','Belapur')
,('Mobiles',25000.25,1000.12,50000.15,'Maharashtra','Thane','Kopari')
,('Mobiles',25000.25,1000.12,50000.15,'Delhi','Noida','Noida')
DECLARE
@Product_Sub_Category NVARCHAR(50)= NULL,
@MinPrice NUMERIC(18,2) = NULL,
@MaxPrice NUMERIC(18,2) = NULL,
@State NVARCHAR(50) = NULL,
@City NVARCHAR(50) = NULL,
@Area NVARCHAR(50) = NULL
SELECT SubCategory
,REPLACE(CONVERT(VARCHAR(15), Price,1),'.00','') AS Price
,State
,City
,Area
FROM @temp
WHERE (@Product_Sub_Category IS NULL OR SubCategory LIKE '%' + @Product_Sub_Category + '%')
AND (CONVERT(VARCHAR(15),Price)
BETWEEN ISNULL(@MinPrice,0) AND @MaxPrice OR (@MaxPrice IS NULL))
AND (@State IS NULL OR State LIKE '%' + @State + '%')
AND (@City IS NULL OR City LIKE '%' + @City + '%')
AND (@Area IS NULL OR Area LIKE '%' + @Area + '%')
Output
SubCategory |
Price |
State |
City |
Area |
Cloths |
152.59 |
Maharashtra |
Mumbai |
Goregoan |
Mobiles |
1568.89 |
Maharashtra |
Pune |
Pimpri |
Electonics |
50000.55 |
Maharashtra |
NaviMumbai |
Belapur |
Mobiles |
25000.25 |
Maharashtra |
Thane |
Kopari |
Mobiles |
25000.25 |
Delhi |
Noida |
Noida |