SQL Server Performance

variables for sorting

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by athanasiusrc, May 4, 2007.

  1. athanasiusrc New Member

    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
  2. ndinakar Member

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

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  3. MohammedU New Member

  4. athanasiusrc New Member

    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
  5. MohammedU New Member

    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.
  6. FrankKalis Moderator

    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
  7. athanasiusrc New Member

    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
  8. FrankKalis Moderator

    And on which column do you want to sort?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  9. athanasiusrc New Member

    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
  10. athanasiusrc New Member

    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
  11. ndinakar Member

    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/
  12. Adriaan New Member

    ... or use dynamic SQL.

Share This Page