4

I'm writing some parameterised queries, and I noticed that using a parameter for the "ASC/DESC" part of the ORDER isn't allowed in SQL Server 2008. This gives the error Incorrect syntax near '@p'.:

declare @p nvarchar
set @p = 'desc'

select * from Customer order by CustomerID @p

However using a parameter for the number of results works fine, provided you use brackets around it.

declare @p int
set @p = 5

select top (@p) * from Customer order by CustomerID

Is there any reason for this, and any workarounds?

This question suggests one workaround, but doesn't say why it's needed: DESC and ASC as a parameter in stored procedure

Community
  • 1
  • 1
kristianp
  • 5,496
  • 37
  • 56
  • 1
    The main difference I see is that `5`is a regular value while `desc` is part of the SQL language. This would turn your query into dynamic or force you to use `case` statements. – BytesOfMetal Aug 03 '16 at 07:50
  • 1
    @MorganBardon posted this link as an answer if it's useful: http://stackoverflow.com/questions/18207954/using-variables-for-asc-and-desc-in-order-by – Tanner Aug 03 '16 at 07:51
  • 1
    Is this question solved? Do you need further help? Please allow me one hint: It would be very kind of you to tick the acceptance check below the best answer's vote counter. This will 1) mark this issue as solved 2) make it easier for followers to find the best solution 3) pay points to the answerer and 4) pay points to you. Once you crossed the 15 points border yourself you are - additionally - asked to vote on contributions. This is the SO-way to say thank you. Happy Coding! – Shnugo Aug 18 '16 at 10:18

4 Answers4

3

Try something like this

DECLARE @tbl TABLE(ID INT IDENTITY,SomeValue VARCHAR(100));

INSERT INTO @tbl(SomeValue) VALUES('a'),('b'),('c');

DECLARE @sortDirection VARCHAR(10)='ASC';

SELECT *
FROM @tbl
ORDER BY CASE WHEN @sortDirection='DESC' THEN ID END DESC
        ,CASE WHEN @sortDirection='ASC' THEN ID END ASC

EDIT

I took away the ELSE 0. It's not needed and would make troubles if the sort column is not INT

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • just to add to the answer, SQL will still sort on both the columns. adding a case helps that when sortDirection is 'ASC', the evaluated `ORDER BY` becomes `ORDER BY 0 DESC, Id ASC` which is really `ORDER BY Id ASC` – ughai Aug 03 '16 at 07:55
  • @strugo, yes, you're right... you have to do something to expression to reverse its sort order... using -id (minus id) works though – Charles Bretana Aug 03 '16 at 07:55
  • 2
    `order by case @sortDirection when 'Desc' then id else -id end` – Charles Bretana Aug 03 '16 at 07:57
  • @CharlesBretana - Nice idea for numerical columns. – ughai Aug 03 '16 at 07:59
  • @CharlesBretana, Nice trick! Tiny hint: You mixed the directions... One draw back: This would not work with non-numeric columns... – Shnugo Aug 03 '16 at 07:59
  • @Shnugo, right. I deleted my original (incorrect) comment. – Charles Bretana Aug 03 '16 at 08:07
  • hah! your moniker is not easy to read and re-spell correctly! – Charles Bretana Aug 03 '16 at 08:09
  • and it would work with dates, and with character data that is not too long.... `order by case @sortDirection when 'desc' then -Cast(Cast(varcharColumn as varbinary) as bigint) else Cast(Cast(varcharColumn as varbinary) as bigint) end` as long as varcharColumn is small enough – Charles Bretana Aug 03 '16 at 08:14
  • @CharlesBretana, With conversions... You'd probably get into *sargability* issues...# – Shnugo Aug 03 '16 at 08:17
  • but with order by you need to read all records in set anyway... so sargability is not issue? – Charles Bretana Aug 03 '16 at 08:19
  • @CharlesBretana, converting to `VARBINARY` might be dangerous (unexpected negativ)? Would have to think about this a bit more... – Shnugo Aug 03 '16 at 08:19
  • @CharlesBretana Oh yes, it is... The usage of indexes is absolutely crucial with sorting, isn't it? – Shnugo Aug 03 '16 at 08:20
  • but sargability has to do with reducing the number of IOs necessary to perform a task, by reading nodes in b-tree to filter records instead of performing a table scan and examining every record. If you have to read every record anyway, processor could use index, but table scan is fewer IOs cause there's only one IO per record, whereas in index there's n IOs per record (where n is number of levels in b-Tree) – Charles Bretana Aug 03 '16 at 08:24
  • Actually, one IO per page in disk image of table. (and we're about to get yelled at for using comments to have conversation...) – Charles Bretana Aug 03 '16 at 08:25
  • @CharlesBretana, you might [read this](https://sqlsunday.com/2014/01/19/sargable-expression-performance/). But I think this discussion is off topic actually, as the `CASE` itself makes it unSARGable already... – Shnugo Aug 03 '16 at 08:29
  • http://josef-richberg.squarespace.com/journal/2010/1/28/is-a-case-statement-considered-sargable.html or http://sqlblog.com/blogs/rob_farley/archive/2010/02/02/a-case-study-in-sargability.aspx – Charles Bretana Aug 03 '16 at 08:34
  • "The thing that is SARGable is the term that is indexed", i.e., the column in the table, not the entire case expression – Charles Bretana Aug 03 '16 at 08:38
  • @CharlesBretana, Good night :-) I'm living in Vienna/Austria/Europe... Started to work just 2 hours ago :-) Good night to you! – Shnugo Aug 03 '16 at 08:52
1

Or you can use a stored procedure:

    DECLARE @sqlQuery AS NVARCHAR(255)
    DECLARE @Order AS NVARCHAR(255) = 'DESC'

    SET @sqlQuery = '
        SELECT *
        FROM dbo.DimCustomer
        ORDER BY CustomerKey ' +  @Order


    EXEC sp_executesql @SQLQuery
Rogier Wijsman
  • 133
  • 7
1

You can use a dynamic SQL. You can prepare a query by code like tihs

declare @top int = 5;
declare @order varchar(4) = 'desc';

declare @sql nvarchar(max) = N'select top (' + cast(@top as varchar(max)) + ') * from Customer order by CustomerID ' + @order;

If you try the select @sql; then you will see that the @sql is

select top (5) * from Customer order by CustomerID desc

Now you can execute your query by sp_executesql function like in this way

execute sp_executesql @sql;
zajonc
  • 1,935
  • 5
  • 20
  • 25
0
SELECT DQ.* FROM (
                 SELECT *,
                       (row_number() 
                           OVER (ORDER BY employeeID)) * 
                              CASE WHEN @sortDirection = 'ASC' THEN 
                                   1 
                              ELSE 
                                   -1 
                              END sort 
                                   from employeemaster) DQ
                   ORDER BY DQ.sort

you could then reverse any sort, including sorts on multiple fields ans sort on nun-numeric

Cato
  • 3,652
  • 9
  • 12
  • The simple `CASE` solution of my answer allows an execution plan using indexes (read about *sargable* or follow the discussion below my answer), yours could get quite slow with big data. And one more point: If you want to sort one column `ASC` and another `DESC` you would get in troubles... – Shnugo Aug 03 '16 at 10:58
  • And btw: Charles suggestion (in comment below my answer) does exactly the same without the unneeded complexity. And - as there - you would get into troubles if the column is not numeric... – Shnugo Aug 03 '16 at 11:01