动态订单方向 [英] Dynamic order direction

问题描述

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

I writing a SP that accepts as parameters column to sort and direction.

我不想使用动态SQL.

I don't want to use dynamic SQL.

问题在于设置了方向参数.

The problem is with setting the direction parameter.

这是部分代码:

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

推荐答案

您可能有两个几乎相同的ORDER BY项，一个ASC和一个DESC，然后扩展您的CASE语句以使一个或他们中的其他人总是等于一个值:

You could have two near-identical ORDER BY items, one ASC and one DESC, and extend your CASE statement to make one or other of them always equal a single value:

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

这篇关于动态订单方向的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！