Solved

help with stored procedure for sorting, paging and filtering

Posted on 2014-03-12
4
430 Views
Last Modified: 2014-03-12
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:

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

Open in new window

0
Comment
Question by:CEL_IT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 33

Accepted Solution

by:
Big Monty earned 500 total points
ID: 39923604
if there are a dynamic amount of options coming through the the SP, then I would pass in all of the parameters as a string, then within the SP, use a sql split function to parse out each value into a temp table. From there, loop through the temp table and buid ypur where clause from that
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39923607
to loop through a table, you can use this code:

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

Open in new window

0
 

Author Comment

by:CEL_IT
ID: 39924335
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.

-- 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,',') ))

Open in new window


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.
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39924402
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.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question