Paremeterized sort on multiple keys?? is it possible

• mario17

  SSCertifiable

  Points: 6079

  More actions

  October 13, 2014 at 10:07 am

  #290840

  HI, all

  I have sp that works with GUI and have an opton for sort field/ collation. But I noteiced that if I do level on very generic key like <Level> in my case it does the job, but output does'nt look right after that all names, account numbers are messed, so I'd like to add second sort column and looks like it can'be be done with my syntax,

  I tried to play adding second column and failed.

  Is there any solution to make it in simple way without adding any extra steps ??

  Select * from T1 order by Level, FirstName

  looks like it only can be done with Dynamic...

  Thanks

  Mario

  DECLARE @SortColmn VARCHAR(10) = 'Level'

  SELECT *

  FROM TABLE

  CASE WHEN @SortColmn = 'FirstName' AND @SortDir = 0 THEN FirstName END DESC

  ,CASE WHEN @SortColmn = 'LastName' AND @SortDir = 0 THEN LastName END DESC

  ,CASE WHEN @SortColmn = 'Region ' AND @SortDir = 0 THEN Region END DESC

  ,CASE WHEN @SortColmn = 'Level' AND @SortDir = 0 THEN [Level] END desc --<@>>< THEN FIrstName asc ???

• mario17

  SSCertifiable

  Points: 6079

  More actions

  October 13, 2014 at 11:48 am

  #1752061

  I actually found a solution, but just for one part of job

  --- this is OK

  ,CASE WHEN @SortColumn = 'Level' AND @SortDir = 1

  THEN CAST(a.[Level] AS VARCHAR(5)) + FirstName END ASC

  ....

  --- s'be Level Desc, FirstName ASC !!!

  CASE WHEN @SortColumn = 'Level' AND @SortDir = 0

  THEN CAST(a.[Level] AS VARCHAR(5)) + FirstName END DESC

  ...

  I think I'll live with what I have, I can't go with dynamics

  Best

  Mario