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
Declare @Id int
While (Select Count(*) From ATable Where Processed = 0) > 0
Begin
Select Top 1 @Id = Id From ATable Where Processed = 0
--Do some processing here
Update ATable Set Processed = 1 Where Id = @Id
End
-- 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,',') ))
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.