Can I store SQL Server sort order in a variable?

Question

I have the following SQL within a stored procedure. Is there a way to remove the IF statement and pass the 'ASC'/'DESC' option as a variable?

I know I could do the query a number of different ways, or return a table and sort it externally etc. I would just like to know if I can avoid duplicating the CASE statement.

IF @sortOrder = 'Desc'
  BEGIN
    SELECT * FROM #t_results
    ORDER BY
    CASE WHEN @OrderBy = 'surname'           THEN surname END DESC,
    CASE WHEN @OrderBy = 'forename'          THEN forename END DESC,
    CASE WHEN @OrderBy = 'fullName'          THEN fullName END DESC,
    CASE WHEN @OrderBy = 'userId'            THEN userId END DESC,
    CASE WHEN @OrderBy = 'MobileNumber'      THEN MSISDN END DESC,
    CASE WHEN @OrderBy = 'DeviceStatus'      THEN DeviceStatus END DESC,
    CASE WHEN @OrderBy = 'LastPosition'      THEN LastPosition END DESC,
    CASE WHEN @OrderBy = 'LastAlert'         THEN LastAlert END DESC,
    CASE WHEN @OrderBy = 'LastCommunication' THEN LastCommunication END DESC,
    CASE WHEN @OrderBy = 'LastPreAlert'      THEN LastPreAlert END DESC 
    END
 ELSE
   BEGIN
    SELECT * FROM #t_results
    ORDER BY
    CASE WHEN @OrderBy = 'surname'           THEN surname END DESC,
    CASE WHEN @OrderBy = 'forename'          THEN forename END DESC,
    CASE WHEN @OrderBy = 'fullName'          THEN fullName END DESC,
    CASE WHEN @OrderBy = 'userId'            THEN userId END DESC,
    CASE WHEN @OrderBy = 'MobileNumber'      THEN MSISDN END DESC,
    CASE WHEN @OrderBy = 'DeviceStatus'      THEN DeviceStatus END DESC,
    CASE WHEN @OrderBy = 'LastPosition'      THEN LastPosition END DESC,
    CASE WHEN @OrderBy = 'LastAlert'         THEN LastAlert END DESC,
    CASE WHEN @OrderBy = 'LastCommunication' THEN LastCommunication END DESC,
    CASE WHEN @OrderBy = 'LastPreAlert'      THEN LastPreAlert END DESC 
    END
END

Answer 1

Pass in @OrderBy int, where positive is ASC, negative is DESC, and the actual number is the column to sort by:

SELECT
    dt.yourColumn1
        ,dt.yourColumn2
        ,dt.yourColumn3
        ,CASE 
            WHEN @OrderBy>0 THEN dt.SortBy
            ELSE NULL
         END AS SortByAsc
        ,CASE 
            WHEN @OrderBy<0 THEN dt.SortBy
            ELSE NULL
         END AS SortByDesc
    FROM (SELECT
              yourColumn1
                  ,yourColumn2
                  ,yourColumn3
                  ,CASE
                      WHEN ABS(@OrderBy) = 1 THEN surname
                      WHEN ABS(@OrderBy) = 2 THEN forename
                      WHEN ABS(@OrderBy) = 3 THEN fullName
                      WHEN ABS(@OrderBy) = 4 THEN CONVERT(varchar(10),userId)
                      WHEN ABS(@OrderBy) = 5 THEN CONVERT(varchar(10),MobileNumber
                      WHEN ABS(@OrderBy) = 6 THEN DeviceStatus
                      WHEN ABS(@OrderBy) = 7 THEN LastPosition
                      WHEN ABS(@OrderBy) = 8 THEN CONVERT(varchar(23),LastAlert,121)
                      WHEN ABS(@OrderBy) = 9 THEN CONVERT(varchar(23),LastCommunication,121)
                      WHEN ABS(@OrderBy) =10 THEN CONVERT(varchar(23),LastPreAlert,121)
                      ELSE NULL
                  END AS SortBy
              FROM YourTablesHere
              WHERE X=Y
         ) dt
    ORDER BY SortByAsc ASC, SortByDesc DESC

Just make sure you build a string that sort properly. Notice I used 'YYYY-MM-DD hh:mm:ss.mmm' for the dates and put the numbers into strings. We usually put multiple columns together, so if you sort by surname, forename is used too, etc. Watch out, if you do combine multiple columns you'll need to pad with zeros or spaces.

If you don't want the SortByAsc and SortByDesc columns to be in the result set, wrap the entire thing in a derived table.