Dynamic ORDER BY

[yakatz]

I am working on adding sorting functionality to an online store.
The query works fine when it has a hard coded ORDER BY, but not when the ORDER BY is a variable.

declare @CatID Int
declare @PageSize Int
declare @PageNumber Int
declare @OrderBy VarChar
declare @EntityName VarChar

set @CatID = 3158
set @PageSize = 10
set @PageNumber = 1
set @OrderBy = 'p.SalePrice'
set @EntityName = 'Category';

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 RowNumber

[debasisdas]

Better use the sorting functionality from the frontend.

[yakatz]

Better use the sorting functionality from the frontend.

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?so rt=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.

[ck9663]

Use dynamic query..

-- CK

[deepuv04]

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?so rt=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.HidePriceUnti lCart 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.ProductCate gory 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*(@Pa geNumber-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.HidePriceUnti lCart)
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