CEL_IT
asked on
help with stored procedure for sorting, paging and filtering
I have created a stored procedure that gets products, filters, sorts and does paging. I am now trying to filter based on product attributes and wondering how to make it dynamic.
I am fetching all attributes based on the search results and all attributes are checkbox with name "filter1". When one of the checkbox is checked, the value is passed onto the procedure as filter1 and this is working so far.
I need to make this dynamic because the method so far only allows a fixed number of parameters.
Attribute tables:
Items_AttributeValues
itemID
attributeValueID
Item_Attribute_Value
id
value
attributeID
Item_Attribute
id
name
Stored procedure so far:
I am fetching all attributes based on the search results and all attributes are checkbox with name "filter1". When one of the checkbox is checked, the value is passed onto the procedure as filter1 and this is working so far.
I need to make this dynamic because the method so far only allows a fixed number of parameters.
Attribute tables:
Items_AttributeValues
itemID
attributeValueID
Item_Attribute_Value
id
value
attributeID
Item_Attribute
id
name
Stored procedure so far:
ALTER PROCEDURE [dbo].[web_search_getProducts]
@searchText as nvarchar (20) = NULL,
@brand as nvarchar (20) = NULL,
@deptID as int = 0,
@catID as int = 0,
@filter1 as int = 0,
@PageNo as integer = 1,
@PageRecs as integer = 50,
@OrdType as integer = 0,
@custID as int = 0
AS
WITH results AS (
SELECT rowNo = ROW_NUMBER() OVER(ORDER BY
case @OrdType when 0 then i.MSRP end Desc,
case @OrdType when 1 then i.MSRP end Asc,
case @OrdType when 2 then i.subdescription1 end Asc,
case @OrdType when 3 then i.subdescription1 end desc),
i.ID AS itemID, i.Description AS itemDesc,
i.ItemLookupCode, c.ID AS catID,
c.Name AS catName, d.ID AS deptID,
d.Name AS deptNAme, ie.Description,
ie.Short_Desc, ie.Video,
ie.Image,
ie.Path,
i.Quantity,
i.MSRP,
i.SubDescription1 as brand,
a.id as AttributeID,
a.name as Attribute,
iav.id as AttributeValueID,
iav.value as AttributeValue
FROM dbo.Item i INNER JOIN dbo.Category c ON i.CategoryID = c.ID
INNER JOIN dbo.Department d ON c.DepartmentID = d.ID
INNER JOIN dbo.Item_Extended ie ON i.ID = ie.RMS_ID
LEFT JOIN dbo.Items_AttributeValues iavs ON iavs.itemID = i.ID
LEFT JOIN dbo.Item_Attribute_Value iav on iav.id = iavs.attributeValueID
LEFT JOIN dbo.Item_Attribute a on a.id = iav.attributeID
-- filtering based on fields
WHERE
(i.Inactive = 0)
AND (@brand IS NULL OR i.Subdescription1 LIKE '%' + @brand + '%')
AND (@deptID = 0 OR d.ID=@deptID)
AND (@catID = 0 OR c.ID=@catID)
AND ((@searchText IS NULL OR i.Description LIKE '%' + @searchText + '%') OR (@searchText IS NULL OR i.ExtendedDescription LIKE '%' +
@searchText + '%'))
-- filter based on attribute value
AND (@filter1 = 0 OR iav.id=@filter1)
)
SELECT * FROM results WHERE rowNo between ((@PageNo-1)*@pagerecs)+1 and @PageNo*@pagerecs
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Big Monty,
thanks for your help earlier, I have now updated it to pass all parameters in a string and use the split function as you suggested which works brilliant.
Now my only issue is same products being returned multiple times. I have done a left join but I think because of the IN statement, I am getting same products multiple times.
thanks for your help earlier, I have now updated it to pass all parameters in a string and use the split function as you suggested which works brilliant.
-- filter 1 changed to @attrs as varchar (500) to pass a string with all parameters
AND (@attrs is null OR iav.id IN ( SELECT Value FROM fnSplitStringToInt(@attrs,',') ))
Now my only issue is same products being returned multiple times. I have done a left join but I think because of the IN statement, I am getting same products multiple times.
I'm sorry, I don't even know where to begin to resolve that issue. I recommend either Requesting Attention on this question or closing it out and asking a new one with the specifics listed in it.
Open in new window