主页 / dba / 问题 / 116575
Accepted
Artashes Khachatryan
Asked: 2015-10-01 01:50:53 +0800 CST

使用输入参数在 SP 中排序

  • 772

我有一个包含 2 列的表：

CREATE TABLE test(
id int, 
name nvarchar(50)
)
go 

insert INTO Test(id, name)
VALUES (1, 'aa')
insert INTO Test(id, name)
VALUES (2, 'bb')
insert INTO Test(id, name)
VALUES (3, 'aa')

我需要一个存储过程，以不同的顺序从我的表中选择数据。

    CREATE PROCEDURE testSelect(
@OrderColumnID int
)    
AS 
BEGIN
    SELECT *
    FROM test
    ORDER BY CASE @OrderColumnID WHEN 1 THEN 1 WHEN 2 THEN 2 END ASC
END

WHEN 1 THEN 1 WHEN 2 THEN 2意味着，如果输入参数 = 1，则表必须按“id”列排序，如果输入参数 = 2，表必须按“名称”列排序。

当我像这样执行 SP 时：

EXEC dbo.testSelect @OrderColumnID = 2

我得到一个不正确的结果。

我不想重写我的 SP 以使用动态 SQL，或者将它分成 if ..... else if .... 块

CREATE PROCEDURE testSelect(
@OrderColumnID int
)    
AS 
BEGIN
    IF @OrderColumnID = 1
    SELECT *
    FROM test
    ORDER BY 1
    ELSE IF @OrderColumnID = 2 
    SELECT *
    FROM test
    ORDER BY 2
END
sql-server sql-server-2014

1 个回答

  1. Best Answer

    此查询意味着，如果 @OrderColumnID = 1 则按 1 排序（作为 int，而不是作为第一列）。所以你得到随机顺序。

    您可以通过以下方式获得预期结果：

    CREATE PROCEDURE testSelect(
    @OrderColumnID int
)
AS 
BEGIN
SELECT *
FROM test
ORDER BY CASE @OrderColumnID WHEN 1 THEN Id END ASC,
    CASE @OrderColumnID WHEN 2 THEN name END ASC
END

    在这里“指定条件订单”阅读更多相关信息。如果在 SQL Server 2008 SP1 CU5 或更高版本上添加索引，ORDER BY CASE...构造可能能够使用索引来避免排序。OPTION (RECOMPILE)

    当然，您可以使用动态 SQL。是这样的：

    CREATE PROCEDURE testSelect(
    @OrderColumnID int
)
AS 
BEGIN
DECLARE @OrderColumnID INT = 2,
    @sql NVARCHAR(MAX)

SET @sql = 'SELECT * FROM test ORDER BY '+CAST(@OrderColumnID AS VARCHAR(2))

EXEC sp_executesql @sql
END

    我认为这是最简单的方法，我不知道你为什么不想这样做（适当保护免受 SQL 注入攻击）。

    • 4

相关问题