sql-server - 按参数排序

Question

嗨，我有一个存储过程，我在其中执行选择查询。我想通过外部参数来订购它。

我发布了一个最小的例子:

CREATE PROCEDURE [dbo].[up_missioni_get_data]
@order VarChar(100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT  * from missioni ORDER BY ...
END

我可以写什么来做到这一点？

谢谢

Answer 1

你有2个选择，要么使用CASE语句，要么使用动态sql

这将是 CASE 语句的示例

DECLARE @Table TABLE(
        Col1 VARCHAR(10),
        Col2 VARCHAR(10)
)

DECLARE @OrderBy VARCHAR(100)

SET @OrderBy = 'Col1'

SELECT  *
FROM    @Table
ORDER BY 
        CASE
            WHEN @OrderBy = 'Col1' THEN Col1
            WHEN @OrderBy = 'Col2' THEN Col2
            ELSE Col1
        END

这将是动态 sql 的示例

CREATE TABLE #Table (
        Col1 VARCHAR(10),
        Col2 VARCHAR(10)
)

DECLARE @OrderBy VARCHAR(100)

SET @OrderBy = 'Col1'

DECLARE @SqlString NVARCHAR(MAX)

SELECT @SqlString = 'SELECT * FROM #Table ORDER BY ' + @OrderBy

EXEC(@Sqlstring)

DROP TABLE #Table