.Net ramblings
# Friday, 31 August 2007
SQL: order by with parameters of different datatype
It's fair enough that SQL won't accept a parameterised query like below, because it cannot verify that the parameter is referring to a valid column.
select * from table order by @OrderBy
the work around then is to use a case statement like so:
select * from table order by case 
when @OrderBy = 'Column1' then Column1
when @OrderBy = 'Column2' then Column2
end
but i ran into a problem with this approach, where sql raises an error if the datatypes of the columns are not all the same, e.g. you may want to sort by an Int or NVarChar column.  the datatype precedence rules applied to the case statement are well explained in this post on google groups.  the solution posted by Erland Sommarskog is to have a separate case statement for each clause.  so the more robust approach is like so:
select * from table order by 
case when @OrderBy = 'Column1' then Column1 end,
case when @OrderBy = 'Column2' then Column2 end




Friday, 31 August 2007 12:36:35 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  .Net General | Database