How to specify a column to order by in a parameter to a stored procedure?

I have a stored procedure to return the top 50 rows from a table, like this:

select top 50 
    Puzzle, 
    min(DateSolved) as CreationDate, 
    avg(SecondsToComplete) as AverageTime, 
    count(*) as TimesSolved 
  from CustomSolves
  group by Puzzle

But I want the stored procedure to accept two parameters, so that I can order by a custom column (the creation date, the average time, or the times sovled) and only select records newer than a certain date. Something like this pseudo-SQL:

procedure SelectCustomPuzzles
  @CutoffDate datetime,
  @SortColumn column
as
  select top 50 
      Puzzle, 
      min(DateSolved) as CreationDate, 
      avg(SecondsToComplete) as AverageTime, 
      count(*) as TimesSolved 
    from CustomSolves
    where CreationDate > @CutOffDate
    group by Puzzle
    order by @SortColumn

How could I go about doing something like this?

Answers


order by 
   CASE @SortColumn WHEN 'foo ASC' THEN foo ELSE NULL END,
   CASE @SortColumn WHEN 'foo DESC' THEN foo ELSE NULL END DESC,
   CASE @SortColumn WHEN 'bar ASC' THEN bar ELSE NULL END,
   CASE @SortColumn WHEN 'bar DESC' THEN bar ELSE NULL END DESC,

Edit, for a default sort add one of these

...
CASE  WHEN @SortColumn IS NULL THEN default ELSE NULL END DESC

...
defautcolumn

Notes:

  • ASC or DESC can't be in the CASE: has to be outside
  • A single CASE with multiple WHEN..THEN column requires datatypes to be implicitly CASTable. I prefer separate CASE expressions to avoid implicit conversions See http://dba.stackexchange.com/a/4166/630 for a worked example

You can use a case:

order by case when @SortColumn = 'Puzzle' then Puzzle end,
         case when @SortColumn = 'PuzzleDesc' then Puzzle end desc,
         ...

The database can't use indexes if you write the query like this. The only way around that is dynamic SQL, like:

declare @sql nvarchar(max)
set @sql = 'select * from tbl1 order by ' + @OrderByVar
exec @sql

Unless you are willing to go into dynamic SQL territory (which would likely be an overkill for something like an order by) consider using case-when:

order by case(@SortColumn)
    when 'ColumnName2' then ColumnName1
    when 'ColumnName2' then ColumnName2
    else ColumnName3
end

As gbn noted below, this would work only because your three sort columns are of the same type. Had it not been like that, you'd need to add a cast (perhaps to sql_variant) in order for the SQL server to accept your query.


Need Your Help

Comparing hash causing issues

php sql-server hash

I have a site that I am storing the username and hashed password in a table. I am trying to compair this information (username and hashed password) to the login information passed from my login site.

Best way to get data from a Server to Android phone

android xml json

I would like to write an Android application which communicates with central server written in php , communicate with it and get/send data, for example user authentication, display of user preferen...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.