代码之家 › 专栏 › 技术社区 › The real napster

存储过程的降序/升序参数

stored-procedures tsql

4

The real napster · 技术社区 · 17 年前

我有以下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的解决方案,也找不到谷歌的任何东西。

如您所见,我有参数sortdirection,我尝试过多种方式使用它,但总是有错误…尝试了case语句、if语句等,但由于我想插入关键字这一事实而变得很复杂。

帮助将是非常宝贵的,我已经尝试了一定的事情,想到了,但没有能够得到正确的。

3 回复 | 直到 17 年前

1

9

Tadmas 17 年前

您可以使用两个按字段排序:

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

如果没有一个when子句匹配,则case的计算结果将为空,这样,对于每一行,两个字段中的一个将计算为空(不影响排序顺序),而另一个具有适当的方向。

不过,一个缺点是您需要复制@sortby case语句。使用带有sp_executesql的动态SQL,并根据参数编写“asc”或“desc”文本,可以实现相同的功能。

2

1

Eoin Campbell 17 年前

这段代码将很快变得无法管理,因为您需要在出现以下情况时加倍嵌套您的案例…一组用于排序依据的列,以及嵌套集,用于它是asc还是desc

最好考虑在这里使用动态SQL…

DECLARE @sql nvarchar(max)
SET @sql = '
        Select 
                tbl_houses.*
        from tbl_houses 
        where 
                postal in (select zipcode from crm_zipcodes where web_region_id = ' + @webRegionID + ') ORDER BY '

SET @sql = @sql + ' ' + @sortBy + ' ' + @sortDirection

EXEC (@sql)

3

0

Robin Day 17 年前

您可以使用一些动态SQL并使用exec调用它。但是,如果用户对参数有任何控制权,请注意SQL注入。

CREATE PROCEDURE GetAllHouses
    set @webRegionID = 2 
    set @sortBy = 'case_no'
    set @sortDirection = 'ASC'
    AS
    BEGIN
    DECLARE @dynamicSQL NVARCHAR(MAX)

    SET @dynamicSQL =
    '
    SELECT
        tbl_houses.*
    FROM
        tbl_houses
    WHERE
        postal
    IN
    (
        SELECT
            zipcode
        FROM
            crm_zipcodes
        WHERE
            web_region_id = ' + CONVERT(nvarchar(10), @webRegionID) + '
    )
    ORDER BY
        ' + @sortBy + ' ' + @sortDirection

    EXEC(@dynamicSQL)

    END
    GO