variables for sorting

Is it possible to use variables for sorting a query?

Declare
@asc varchar(4),
@sortby varchar(20)

SELECT
*
FROM
table
ORDER BY
@Sortby @ASC

I can't get this to work so far but want the query to dynamically change the sort method.

Webmaster
Aquinas and More Catholic Goods
www.aquinasandmore.com

No. if you have a list of columns you can use the CASE statement.

***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

YOu can use dynamic sql to do this....
Also check the following....
http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Thanks! That will work great for columns that are in the table but what about columns that are calculated by the query? The case statement doesn't like those.

Webmaster
Aquinas and More Catholic Goods
www.aquinasandmore.com

In that case populate a temp table or table variable then use the case statement on that...


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

ALTER PROCEDURE [dbo].[pr_getadvertisingReportByDateRange]
@StartDate varchar(10),
@EndDate varchar(10),
@Col varchar(20),
@Dir varchar(5)
AS

SELECT
dbrderSources.OrderSource,
sum(ItemPrice * (orderdetails.QTYOrdered-orderdetails.QTYReturned)) as total,
dbrderSources.OrderSource_Pk,
dbrderSourceSites.OrderSourceSite,
count(distinct(dbo.orders.order_pk)) as ordertotal,
(sum(ItemPrice * (orderdetails.QTYOrdered-orderdetails.QTYReturned)))/ (count(distinct(dbo.orders.order_pk))) as avgPerOrder,
sum((ItemPrice-ItemCost) * (orderdetails.QTYOrdered-orderdetails.QTYReturned)) as Profit,
(sum((ItemPrice-ItemCost) * (orderdetails.QTYOrdered-orderdetails.QTYReturned))) / (sum(ItemPrice * (orderdetails.QTYOrdered-orderdetails.QTYReturned))) as percentProfit
FROM
dbrders
INNER JOIN
dbrderSources
ON
dbrderSources.OrderSource_Pk = dbrders.OrderSource_Fk
inner join
dbo.orderstatuses
on
dbo.orders.orderstatus_fk = orderstatuses.orderstatus_pk
INNER JOIN
dbrderSourceSites
ON
dbrderSources.OrderSourceSite_Fk = dbrderSourceSites.OrderSourceSite_Pk
INNER JOIN
OrderDetails
ON
Orders.Order_Pk = Order_Fk
WHERE
(dbrderSources.OrderSource_Pk > 1)
AND
(dbrders.OrderDate > @StartDate)
AND
(dbrders.OrderDate < @EndDate)
AND
pending = 0
GROUP BY
dbrderSources.OrderSource,
dbrderSources.OrderSource_Pk,
dbrderSourceSites.OrderSourceSite
ORDER BY
CASE @dir
WHEN 'desc' THEN
CASE @col
WHEN 'ORDERSOURCE' THEN ORDERSOURCE
WHEN 'ORDERSOURCESITE' THEN ORDERSOURCESITE
WHEN 'ORDERSOURCE_PK' THEN ORDERSOURCE_PK
WHEN 'ORDERTOTAL' THEN ORDERTOTAL
WHEN 'AvgPerOrder' THEN AvgPerOrder
WHEN 'PROFIT' THEN PROFIT
WHEN 'TOTAL' THEN Total
WHEN 'PercentProfit' THEN PercentProfit
END
END
DESC,
CASE @dir
WHEN 'asc' THEN
CASE @col
WHEN 'ORDERSOURCE' THEN ORDERSOURCE
WHEN 'ORDERSOURCESITE' THEN ORDERSOURCESITE
WHEN 'ORDERSOURCE_PK' THEN ORDERSOURCE_PK
WHEN 'ORDERTOTAL' THEN ORDERTOTAL
WHEN 'AvgPerOrder' THEN AvgPerOrder
WHEN 'PROFIT' THEN PROFIT
WHEN 'TOTAL' THEN Total
WHEN 'PercentProfit' THEN PercentProfit
END
END

Webmaster
Aquinas and More Catholic Goods
www.aquinasandmore.com

I would like to be able to sort on any of the columns based on what is submitted to the sp by the @sortby variable.

Webmaster
Aquinas and More Catholic Goods
www.aquinasandmore.com

I can get this to compile but it doesn't run properly.

Error message: Msg 235, Level 16, State 0, Procedure pr_getadvertisingReportByDateRange, Line 49
Cannot convert a char value to money. The char value has incorrect syntax.


ALTER PROCEDURE [dbo].[pr_getadvertisingReportByDateRange]
@StartDate varchar(10),
@EndDate varchar(10),
@Col varchar(20),
@Dir varchar(5)
AS

SELECT
dbrderSources.OrderSource,
sum(ItemPrice * (orderdetails.QTYOrdered-orderdetails.QTYReturned)) as total,
dbrderSources.OrderSource_Pk,
dbrderSourceSites.OrderSourceSite,
count(distinct(dbo.orders.order_pk)) as ordertotal,
(sum(ItemPrice * (orderdetails.QTYOrdered-orderdetails.QTYReturned)))/ (count(distinct(dbo.orders.order_pk))) as avgPerOrder,
sum((ItemPrice-ItemCost) * (orderdetails.QTYOrdered-orderdetails.QTYReturned)) as Profit,
(sum((ItemPrice-ItemCost) * (orderdetails.QTYOrdered-orderdetails.QTYReturned))) / (sum(ItemPrice * (orderdetails.QTYOrdered-orderdetails.QTYReturned))) as percentProfit
INTO #Tempadrevenue
FROM
dbrders
INNER JOIN
dbrderSources
ON
dbrderSources.OrderSource_Pk = dbrders.OrderSource_Fk
inner join
dbo.orderstatuses
on
dbo.orders.orderstatus_fk = orderstatuses.orderstatus_pk
INNER JOIN
dbrderSourceSites
ON
dbrderSources.OrderSourceSite_Fk = dbrderSourceSites.OrderSourceSite_Pk
INNER JOIN
OrderDetails
ON
Orders.Order_Pk = Order_Fk
WHERE
(dbrderSources.OrderSource_Pk > 1)
AND
(dbrders.OrderDate > @StartDate)
AND
(dbrders.OrderDate < @EndDate)
AND
pending = 0
GROUP BY
dbrderSources.OrderSource,
dbrderSources.OrderSource_Pk,
dbrderSourceSites.OrderSourceSite

SELECT
OrderSource,
OrderSourceSite,
OrderSource_Pk,
AvgPerOrder,
Profit,
Total,
PercentProfit
FROM
#Tempadrevenue
ORDER BY
CASE @dir
WHEN 'desc' THEN
CASE @col
WHEN 'ORDERSOURCE' THEN ORDERSOURCE
WHEN 'ORDERSOURCESITE' THEN ORDERSOURCESITE
WHEN 'ORDERSOURCE_PK' THEN ORDERSOURCE_PK
WHEN 'ORDERTOTAL' THEN ORDERTOTAL
WHEN 'AvgPerOrder' THEN AvgPerOrder
WHEN 'PROFIT' THEN PROFIT
WHEN 'TOTAL' THEN Total
WHEN 'PercentProfit' THEN PercentProfit
END
END
DESC,
CASE @dir
WHEN 'asc' THEN
CASE @col
WHEN 'ORDERSOURCE' THEN ORDERSOURCE
WHEN 'ORDERSOURCESITE' THEN ORDERSOURCESITE
WHEN 'ORDERSOURCE_PK' THEN ORDERSOURCE_PK
WHEN 'ORDERTOTAL' THEN ORDERTOTAL
WHEN 'AvgPerOrder' THEN AvgPerOrder
WHEN 'PROFIT' THEN PROFIT
WHEN 'TOTAL' THEN Total
WHEN 'PercentProfit' THEN PercentProfit
END
END
DROP TABLE #tempadrevenue

Webmaster
Aquinas and More Catholic Goods
www.aquinasandmore.com

I think it should be more like:


CASE @dir WHEN 'desc' THEN
(CASE.... THEN OrderSource DESC
THEN ORDERSOURCESITE DESC
END)



The DESC should be inside the CASE along with the column and not outside the CASE.

Also, this kind of sorting is best done at the front end where its easier to manage than this maze of nested CASE's.

***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

... or use dynamic SQL.