variables for sorting | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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.
quote:Originally posted by athanasiusrc 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
Please post your code and I’m sure there is a solution for this. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
ALTER PROCEDURE [dbo].[pr_getadvertisingReportByDateRange]
@StartDate varchar(10),
@EndDate varchar(10),
@Col varchar(20),
@Dir varchar(5)
AS SELECT
db:confused:rderSources.OrderSource,
sum(ItemPrice * (orderdetails.QTYOrdered-orderdetails.QTYReturned)) as total,
db:confused:rderSources.OrderSource_Pk,
db:confused:rderSourceSites.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
db:confused:rders
INNER JOIN
db:confused:rderSources
ON
db:confused:rderSources.OrderSource_Pk = db:confused:rders.OrderSource_Fk
inner join
dbo.orderstatuses
on
dbo.orders.orderstatus_fk = orderstatuses.orderstatus_pk
INNER JOIN
db:confused:rderSourceSites
ON
db:confused:rderSources.OrderSourceSite_Fk = db:confused:rderSourceSites.OrderSourceSite_Pk
INNER JOIN
OrderDetails
ON
Orders.Order_Pk = Order_Fk
WHERE
(db:confused:rderSources.OrderSource_Pk > 1)
AND
(db:confused:rders.OrderDate > @StartDate)
AND
(db:confused:rders.OrderDate < @EndDate)
AND
pending = 0
GROUP BY
db:confused:rderSources.OrderSource,
db:confused:rderSources.OrderSource_Pk,
db:confused:rderSourceSites.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
And on which column do you want to sort? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
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
db:confused:rderSources.OrderSource,
sum(ItemPrice * (orderdetails.QTYOrdered-orderdetails.QTYReturned)) as total,
db:confused:rderSources.OrderSource_Pk,
db:confused:rderSourceSites.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
db:confused:rders
INNER JOIN
db:confused:rderSources
ON
db:confused:rderSources.OrderSource_Pk = db:confused:rders.OrderSource_Fk
inner join
dbo.orderstatuses
on
dbo.orders.orderstatus_fk = orderstatuses.orderstatus_pk
INNER JOIN
db:confused:rderSourceSites
ON
db:confused:rderSources.OrderSourceSite_Fk = db:confused:rderSourceSites.OrderSourceSite_Pk
INNER JOIN
OrderDetails
ON
Orders.Order_Pk = Order_Fk
WHERE
(db:confused:rderSources.OrderSource_Pk > 1)
AND
(db:confused:rders.OrderDate > @StartDate)
AND
(db:confused:rders.OrderDate < @EndDate)
AND
pending = 0
GROUP BY
db:confused:rderSources.OrderSource,
db:confused:rderSources.OrderSource_Pk,
db:confused:rderSourceSites.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.
]]>