Dynamic order direction:如何设置方向参数?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (38)

我编写了一个SP,它接受作为参数列的排序和方向。

我不想使用动态SQL。

问题是如何设置方向参数。

这是部分代码:

SET @OrderByColumn = 'AddedDate'
SET @OrderDirection=1;
.
.
.
 ORDER BY 
      CASE WHEN @OrderByColumn='AddedDate' THEN CONVERT(varchar(50),AddedDate)          
           WHEN @OrderByColumn='Visible' THEN CONVERT(varchar(2), Visible)    
           WHEN @OrderByColumn='AddedBy' THEN AddedBy       
           WHEN @OrderByColumn='Title' THEN Title       
      END
提问于
用户回答回答于

您可以按项目获得两个几乎相同的订单,一个ASC和一个DESC,并扩展您的case语句,使其中一个或另一个始终等于一个值:

ORDER BY 
      CASE WHEN @OrderDirection=0 THEN 1
      ELSE
           CASE WHEN @OrderByColumn='AddedDate' THEN CONVERT(varchar(50),AddedDate)          
                WHEN @OrderByColumn='Visible' THEN CONVERT(varchar(2), Visible)    
                WHEN @OrderByColumn='AddedBy' THEN AddedBy           
                WHEN @OrderByColumn='Title' THEN Title
           END
      END ASC,
      CASE WHEN @OrderDirection=1 THEN 1
      ELSE
           CASE WHEN @OrderByColumn='AddedDate' THEN CONVERT(varchar(50),AddedDate)          
                WHEN @OrderByColumn='Visible' THEN CONVERT(varchar(2), Visible)    
                WHEN @OrderByColumn='AddedBy' THEN AddedBy           
                WHEN @OrderByColumn='Title' THEN Title
           END
      END DESC
用户回答回答于

我使用乘法器再次简化了实际的SELECT语句,并减少了按顺序进行Rbar评估的次数。

DECLARE @multiplier int;

SELECT @multiplier = CASE @Direction WHEN 1 THEN -1 ELSE 1 END;

SELECT
     Columns you actually want
FROM
    (
    SELECT
         Columns you actually want,
         ROW_NUMBER() OVER (ORDER BY AddedDate) AS AddedDateSort,
         ROW_NUMBER() OVER (ORDER BY Visible) AS VisibleSort,
         ROW_NUMBER() OVER (ORDER BY AddedBy) AS AddedBySort,
         ROW_NUMBER() OVER (ORDER BY Title) AS TitleSort
    FROM
         myTable
    WHERE
         MyFilters...
    ) foo
ORDER BY
     CASE @OrderByColumn
        WHEN 'AddedDate' THEN AddedDateSort
        WHEN 'Visible' THEN VisibleSort    
        WHEN 'AddedBy' THEN AddedBySort
        WHEN 'Title' THEN TitleSort
     END * @multiplier;

扫码关注云+社区

领取腾讯云代金券