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?
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
- 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.