DESCENDING/ASCENDING 存储过程的参数 [英] DESCENDING/ASCENDING Parameter to a stored procedure

问题描述

我有以下 SP

CREATE PROCEDURE GetAllHouses
    set @webRegionID = 2
    set @sortBy = 'case_no'
    set @sortDirection = 'ASC'

    AS
    BEGIN

        Select 
            tbl_houses.*
        from tbl_houses 
        where 
            postal in (select zipcode from crm_zipcodes where web_region_id = @webRegionID)
        ORDER BY 
            CASE UPPER(@sortBy) 
                    when 'CASE_NO' then case_no 
                    when 'AREA' then area 
                    when 'FURNISHED' then furnished 
                    when 'TYPE' then [type] 
                    when 'SQUAREFEETS' then squarefeets 
                    when 'BEDROOMS' then bedrooms 
                    when 'LIVINGROOMS' then livingrooms 
                    when 'BATHROOMS' then bathrooms 
                    when 'LEASE_FROM' then lease_from 
                    when 'RENT' then rent 
                    else case_no 
            END 
    END
    GO

现在该 SP 中的所有内容都可以正常工作，但我希望能够选择是按升序排序还是降序排序.我真的找不到使用 SQL 的解决方案，也无法在 google 中找到任何东西.

Now everything in that SP works but I want to be able to choose whether I want to sort ASCENDING or DESCENDING. I really can't fint no solution for that using SQL and can't find anything in google.

正如你所看到的，我有参数 sortDirection，我尝试以多种方式使用它，但总是有错误......尝试过的案例语句、IF 语句等等，但由于我想插入一个关键字.

As you can see I have the parameter sortDirection and I have tried using it in multiple ways but always with errors... Tried Case Statements, IF statements and so on but it is complicated by the fact that I want to insert a keyword.

我会非常感谢您的帮助，我已经尝试了很多我想到的事情，但一直没能把它做好.

Help will be very much appriciated, I have tried must of the things that comes into mind but haven't been able to get it right.

推荐答案

您可以使用两个 order by 字段:

You could use two order by fields:

CASE @sortDir WHEN 'ASC' THEN
    CASE UPPER(@sortBy)
        ...
    END
END ASC,
CASE @sortDir WHEN 'DESC' THEN
    CASE UPPER(@sortBy)
        ...
    END
END DESC

如果 WHEN 子句都不匹配，则 CASE 将评估为 NULL，因此这会导致两个字段中的一个对每一行评估为 NULL(不影响排序顺序)，另一个具有适当的方向.

A CASE will evaluate as NULL if none of the WHEN clauses match, so that causes one of the two fields to evaluate to NULL for every row (not affecting the sort order) and the other has the appropriate direction.

但是，一个缺点是您需要复制 @sortBy CASE 语句.您可以使用带有 sp_executesql 的动态 SQL 并根据参数编写ASC"或DESC"文字来实现相同的目的.

One drawback, though, is that you'd need to duplicate your @sortBy CASE statement. You could achieve the same thing using dynamic SQL with sp_executesql and writing a 'ASC' or 'DESC' literal depending on the parameter.

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