Início / dba / Perguntas / 116575
Accepted
Artashes Khachatryan
Asked: 2015-10-01 01:50:53 +0800 CST

Ordenar por em SP com parâmetro de entrada

  • 772

Tenho uma tabela com 2 colunas:

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')

Preciso de um procedimento armazenado que selecione dados da minha tabela em ordens diferentes.

    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 2significa que, se input param = 1, a tabela deve ser ordenada pela coluna 'id', se input param = 2, a tabela deve ser ordenada pela coluna 'name'.

Quando executo o SP como:

EXEC dbo.testSelect @OrderColumnID = 2

Obtenho um resultado incorreto.

Não quero reescrever meu SP para usar SQL dinâmico ou separá-lo em if ..... else if .... blocos como

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 respostas

  1. Best Answer

    Esta consulta significa que, se @OrderColumnID = 1, ordene por 1 (como int, não como primeira coluna). Então você obtém ordem aleatória.

    Você pode obter o resultado esperado por:

    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

    Leia mais sobre isso aqui "Especificando uma ordem condicional" . A ORDER BY CASE...construção pode usar índices para evitar uma classificação se você adicionar um OPTION (RECOMPILE)no SQL Server 2008 SP1 CU5 ou posterior.

    Claro, você pode usar SQL dinâmico. Algo assim:

    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

    Acho que é a maneira mais fácil e não sei por que você não quer fazer isso (com proteção adequada contra ataques de injeção de SQL).

    • 4

relate perguntas