Quote:
I'm sorry, but I did not understand your answer.
I am writing a package that will create the sorting method seen from the front-end.
I have an xml package that runs a sql query and formats the results.
It will take request parameters from the query string and put them in a sql variables. i have the following query string: product.aspx?sort=1
which is converted by a static list to the required columns to sort (in this case p.SalePrice). sql will not honor the orderby for the variable.
|
Hi,
You can use CASE statement in ORDER BY clause for dynamic sorting.
code is :
[code = sql]
declare @CatID Int
declare @PageSize Int
declare @PageNumber Int
declare @OrderBy VarChar
declare @EntityName VarChar
declare @sort int
set @CatID = 3158
set @PageSize = 10
set @PageNumber = 1
set @OrderBy = Rank() OVER (ORDER BY P.SalePrice)
set @EntityName = 'Category';
Set @sort = 1 --( assign the value passed as a parameter)
WITH ProductPaging AS (
SELECT
ROW_NUMBER() OVER ( ORDER BY @OrderBy ) AS RowNumber,
p.ProductID AS ProductID,
p.Name AS Name,
p.SKU AS SKU,
p.HidePriceUntilCart AS HidePrice,
pc.CategoryID AS CategoryID,
pc.DisplayOrder AS pcDisplayOrder,
p.Price AS Price,
p.SalePrice AS SalePrice
FROM
dbo.Product AS p INNER JOIN
dbo.ProductCategory AS pc ON p.ProductID = pc.ProductID
WHERE
(pc.CategoryID = @CatID) AND
(p.Published = 1) AND
(p.Deleted = 0)
)
SELECT *
FROM
ProductPaging
WHERE RowNumber BETWEEN (@PageSize*(@PageNumber-1))+1 AND (@PageSize * @PageNumber)
ORDER BY
(CASE @Sort WHEN 1 THEN Rank() OVER (ORDER BY P.SalePrice)
WHEN 2 THEN Rank() OVER (ORDER BY P.Price)
WHEN 3 THEN Rank() OVER (ORDER BY Pc.DisplayORder)
WHEN 4 THEN Rank() OVER (ORDER BY Pc.CategoryID)
WHEN 5 THEN Rank() OVER (ORDER BY P.HidePriceUntilCart)
WHEN 4 THEN Rank() OVER (ORDER BY P.SKU)
WHEN 5 THEN Rank() OVER (ORDER BY P.Name)
ELSE Rank() OVER (ORDER BY P.ProductID) end )
[/code]
Thanks
