我可以将 SQL Server 排序顺序存储在变量中吗? [英] Can I store SQL Server sort order in a variable?

问题描述

我在存储过程中有以下 SQL.有没有办法删除 IF 语句并将 'ASC'/'DESC' 选项作为变量传递?

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?

我知道我可以通过多种不同的方式进行查询，或者返回一个表并在外部对其进行排序等.我只想知道我是否可以避免重复 CASE 语句.

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

推荐答案

传入@OrderBy int，其中正数为ASC，负数为DESC，实际数字为要排序的列:

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

只要确保你构建了一个正确排序的字符串.请注意，我使用 'YYYY-MM-DD hh:mm:ss.mmm' 作为日期并将数字放入字符串中.我们通常将多列放在一起，因此如果您按姓氏排序，也会使用名字等.请注意，如果您将多列组合在一起，则需要用零或空格填充.

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.

如果您不希望 SortByAsc 和 SortByDesc 列出现在结果集中，请将整个内容包装在派生表中.

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

这篇关于我可以将 SQL Server 排序顺序存储在变量中吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！