Sorting ASC or DESC is the option

[rac2]

Is there a way to write a SELECT ... ORDER BY query so that the sort order is controlled by a variable? The solution I have is to use IF and run one query if the sort should be ascending and run the other query if it should be descending. This is in a stored procedure.

Here is what I am doing. Is there a way to do this in one statement?

IF @sortDirection = 1

SELECT very, interesting, material
FROM aBunch of tables
ORDER BY 
         CASE
           WHEN @sortField = 1 THEN very
           WHEN @sortField = 2 THEN interesting
           ELSE @sortField = 3 THEN material
         END DESC

ELSE

SELECT very, interesting, material
FROM aBunch of tables
ORDER BY 
         CASE
           WHEN @sortField = 1 THEN very
           WHEN @sortField = 2 THEN interesting
           ELSE @sortField = 3 THEN material
         END ASC

 

[apexbs]

Use Dynamic SQL i.e. For example use NorthWind database.

Declare @L_SQL varchar(7000)
Declare @L_SortOrder varchar(10)

SET @L_SortOrder = 'Asc'

SET @L_SQL = 'SELECT * from Products Order By ProductID ' + @L_SortOrder

Exec ( @L_SQL )

I hope it works for you.

 

[vongrunt]

There is an error in both CASE statement... replace last ELSE with WHEN.

Also: very/interesting/material must be of the same data type and probably (dunno, check it) length.

 

[aalmeida]

to solve the data type issue you can declare the variable as a varchar(10) and set it's value to 1

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[ESquared]

Forget dynamic SQL.

SELECT very, interesting, material
FROM aBunch of tables
ORDER BY 
   CASE @sortDirection WHEN 0 THEN
      CASE @sortField
         WHEN 1 THEN very
         WHEN 2 THEN interesting
         WHEN 3 THEN material
         ELSE 0
      END
   END ASC,
   CASE @sortDirection WHEN 1 THEN
      CASE @sortField
         WHEN 1 THEN very
         WHEN 2 THEN interesting
         WHEN 3 THEN material
         ELSE 0
      END
   END DESC

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[rac2]

The point about the datatype is important, I ran into that. It can be handled by CONVERTing explicitly. It appears that the length does not matter as I have A VARCHAR(50) column, not converted; a TINYINT that I explicitly converted to CHAR(1); and a DATETIME converted to VARCHAR(10). Perhaps SQL Server is still doing some implicit conversions for me, I cant tell. But converting the various datatypes to similar variable character types eliminated the conversion errors I was getting.

The dynamic query is certainly an option. But is that re-compiled every time the procedure runs? That is what I want to avoid.

 

[ESquared]

For what it's worth I think the client should be sorting columns, not the server...

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[rac2]

Oohhh, a nested CASE expression. I like that. And the simple CASE function really cleans it up. Thank you much.

SELECT very, interesting, material
FROM aBunch of tables
ORDER BY 
   CASE @sortDirection
      WHEN 0 THEN
         CASE @sortField
            WHEN 1 THEN CONVERT(VARCHAR(10), very)
            WHEN 2 THEN CONVERT(CHAR(1), interesting)
            WHEN 3 THEN material
         END
      ELSE NULL
   END ASC,
   CASE @sortDirection
      WHEN 1 THEN
         CASE @sortField
            WHEN 1 THEN CONVERT(VARCHAR(10), very)
            WHEN 2 THEN CONVERT(CHAR(1), interesting)
            WHEN 3 THEN material
         END
      ELSE NULL
   END DESC

 

[ESquared]

We both could have left the ELSEs out, I think.

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[aalmeida]

true the Elses aren't really doing anithing other confusion

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"