CODEWITHSUNDEEP
sql-serverdatabasesql-server-2005
Dan Herbert
Dan Herbert

Reputation: 103377

Is it possible to order by any column given a stored procedure parameter in SQL Server?

I was looking into sorting tables by a column designated given some input, and from what I've found, there is no easy way to do this. The best I've found is a switch statement: 

SELECT Column1, Column2, Column3, Column4
FROM Table
ORDER BY CASE WHEN @OrderBY = 'Column1' THEN Column1
              WHEN @OrderBY = 'Column2' THEN Column2
              WHEN @OrderBY = 'Column3' THEN Column3
              WHEN @OrderBY = 'Column4' THEN Column4

Is it possible to do this without having a CASE statement like that? If the table gets bigger and more columns need to be sorted by, this could become messy.

The only way I've been able to do this is by just concatenating a big SQL string, which sort of defeats the advantages of Stored Procedures, and makes the SQL hard to write and maintain.

Upvotes: 4

Views: 1216

Answers (5)

massimogentilini
massimogentilini

Reputation: 4172

The RANK feature of SQL Server and Oracle can improve performance and makes the code a little cleaner:

SQL:

DECLARE @column varchar(10)

SET @column = 'D'

SELECT *
FROM Collection.Account AS A
ORDER BY 
      CASE 
            WHEN @column = 'A' THEN (RANK() OVER(ORDER BY A.Code ASC))
            WHEN @column = 'D' THEN (RANK() OVER(ORDER BY A.Code DESC))
      END

Upvotes: 1

Harun
Harun

Reputation: 11

You already write the correct syntax:

SELECT Column1, Column2, Column3 
FROM SOME_TABLE
ORDER BY 1,2,3

try it

Upvotes: 1

Pittsburgh DBA
Pittsburgh DBA

Reputation: 6772

In this case, unless you have an extremely large dataset and you need to leverage the power of the database server (thin client, weak client machine, etc), it is best to sort within the client.

Upvotes: -2

Mitch Wheat
Mitch Wheat

Reputation: 300489

You have two choices:

  1. As you have implemented above

  2. Or generate dynamic sql and execute using sp_executesql

Upvotes: 4

Ron Savage
Ron Savage

Reputation: 11079

I generally convert the stored procedure to a function that returns a table ( so you can select FROM it ... and add the dynamic order by columns to it in the application code:

Select
    *
From
   myTableFUnction()
Order by
   1, 2, 3, 6  <-- defined by application code in the SQL for the query

Ron

Upvotes: 1

Related Questions