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

Yes, but you have to use Dynamic Queries.

Take a look here.

Answer 2

pass in @OrderBy int, where positive is ASC, negative is DESC, 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 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.

Answer 3

You can do it without dynamic SQL...

SELECT
     *
FROM
     My_Table
WHERE
     Whatever = @something
ORDER BY
     CASE @sort_order
          WHEN 'ASC' THEN
               CASE @order_by
                    WHEN 'surname' THEN surname
                    WHEN 'forename' THEN forename
                    WHEN 'fullname' THEN fullname
                    ELSE surname
               END
          ELSE '1'
     END ASC,
     CASE @sort_order
          WHEN 'DESC' THEN
               CASE @order_by
                    WHEN 'surname' THEN surname
                    WHEN 'forename' THEN forename
                    WHEN 'fullname' THEN fullname
                    ELSE surname
               END
          ELSE '1'
     END DESC

Answer 4

Working Fine with some columns with integers only, how can i make it work in other columns with Strings? thanks