DESC 和 ASC 作为存储过程中的参数 [英] DESC and ASC as a parameter in stored procedure

问题描述

我使用以下 SP 对新闻文章列表进行分页.您可能会猜到，@count 是要返回的行数，@start 是从中选择行的索引(按内部查询排序)，@orderby 表示要排序的列，@orderdir 表示是按一个方向排序还是另一个方向排序.我原来的查询是此处，在我添加 @orderdir 参数之前.

I have the following SP that I am using to paginate a list of news articles. As you may be able to guess, @count is the number of rows to return, @start is the index to select rows from (sorted by inner query), @orderby indicates the column to sort by, and @orderdir indicates whether to sort one direction or the other. My original query was here, before I added the @orderdir parameter.

ALTER PROCEDURE [mytable].[news_editor_paginate]
    @count int,
    @start int,
    @orderby int,
    @orderdir int
AS 
BEGIN
    SET NOCOUNT ON; 
    SELECT TOP (@count) * FROM 
    (  
        SELECT ne.*,n.publishstate, 
            (CASE WHEN @orderdir = 1 THEN
                ROW_NUMBER() OVER (
                    ORDER BY                    
                        CASE WHEN @orderby = 0 THEN ne.[time] END DESC,
                        CASE WHEN @orderby = 1 THEN ne.lastedit END DESC,    
                        CASE WHEN @orderby = 2 THEN ne.title END ASC
                    )
            WHEN @orderdir = 2 THEN
                ROW_NUMBER() OVER (
                    ORDER BY                    
                        CASE WHEN @orderby = 0 THEN ne.[time] END ASC,    
                        CASE WHEN @orderby = 1 THEN ne.lastedit END ASC,
                        CASE WHEN @orderby = 2 THEN ne.title END DESC
                    )
                END
            ) AS num
            FROM news_edits AS ne
            LEFT OUTER JOIN news AS n
            ON n.editid = ne.id 
        ) 
     AS a
    WHERE num > @start
END

现在实际上没有任何问题，但是 @orderby 参数不起作用.如果提供 1 作为 @orderdir 参数，它会给我完全相同的结果，就像我提供 2 作为该参数一样.

Now nothing actually goes wrong, but the @orderby parameter doesn't work. If provide 1 as the @orderdir parameter, it will give me the exact same results as if I provide 2 as that parameter.

推荐答案

不会对每一行都计算行号，但是 case 语句使您在任何情况下都会被 rownum 卡住.

Row number isn't evaluated on every row, however case statements are so you're stuck with the rownum no matter what the case.

试试这个:

            ROW_NUMBER() OVER (
                ORDER BY                    
                    CASE WHEN @orderby = 0 AND @orderdir = 1 THEN ne.[time] END DESC,    
                    CASE WHEN @orderby = 0 AND @orderdir = 2 THEN ne.[time] END ASC,    
                    CASE WHEN @orderby = 1 AND @orderdir = 1 THEN ne.lastedit END DESC,
                    CASE WHEN @orderby = 1 AND @orderdir = 2 THEN ne.lastedit END ASC,
                    CASE WHEN @orderby = 2 AND @orderdir = 1 THEN ne.title END ASC
                    CASE WHEN @orderby = 2 AND @orderdir = 2 THEN ne.title END DESC
                )

这篇关于DESC 和 ASC 作为存储过程中的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！