Tim Mackey's Weblog

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