SQL Server – Dynamic Order By Clause

Yes, I’m too busy for SQL scripting job these recent weeks. I have a union select query that needed to be sorted per union but since it is not allowed to have order by in my unions, I have to resort sorting outside the union using dynamic order by clause.

It is really easy because there is a pre-determined number of fields and that only the field change. My query need to be sorted by two fields and those fields depends on a certain variable.

Here is the order by part:

1 2 3

order by     case when @result_option = 1 then delivery_date else delivery_date_08 end,     case when @result_option = 1 then name_abbr else name_kanji end