CODEWITHSUNDEEP
sql-serverdynamicsql-order-by
Ram Singh
Ram Singh

Reputation: 6918

How to set order by dynamically in SQL Server?

I want to set order by clause dynamically. I have tried it by using case.

But after few cases its not working for me and I am not getting why this is happening. I have tried the following link http://www.sqlteam.com/article/dynamic-order-by this is working only for 5-6 cases after that I am getting errors of conversion.

ORDER BY CASE WHEN @OrderBY = 1 THEN Pla_BattingAverage
          WHEN @OrderBY = 2 THEN Pla_HomeRuns
          WHEN @OrderBY = 3 THEN Pla_RBIs
          WHEN @OrderBY = 4 THEN Pla_StolenBases
          END DESC

Upvotes: 1

Views: 1069

Answers (2)

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

One of solution was suggested by Mark Byers.

You can also use ROW_NUMBER() and sort by result of ROW_NUMBER() function (still one ROW_NUMBER() for each type of order).

The other one is to save result of first query (I assume there is no TOP clause) in temporary table and then use IF to return result.

Something like:

INSERT INTO #results
SELECT
  a, b, c
FROM
  table
...

IF @OrderBY = 1 THEN
  SELECT * FROM #results ORDER BY Pla_BattingAverage DESC
END

IF @OrderBY = 2 THEN
  SELECT * FROM #results ORDER BY Pla_HomeRuns DESC
END

...

However if performance matters I would try generating query dynamically.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838216

You can't for example mix strings and integers in the results of a case expression.

You could try something like this instead:

ORDER BY CASE WHEN @OrderBY = 1 THEN Pla_BattingAverage END DESC,
         CASE WHEN @OrderBY = 2 THEN Pla_HomeRuns END DESC,
         CASE WHEN @OrderBY = 3 THEN Pla_RBIs END DESC,
         CASE WHEN @OrderBY = 4 THEN Pla_StolenBases END DESC

Note that this will be slow as an index cannot be used. Generating the query dynamically could give better performance.

Upvotes: 1

Related Questions