如何创建含多字段动态排序的SQL存储过程？语法修正方案

我尝试创建一个包含多排序字段的存储过程，排序字段与排序方向通过存储过程参数传入，但ORDER BY部分语法不正确，请问该如何修正？是否可以按照我原本的思路实现？

存储过程代码如下：

CREATE PROCEDURE GetFilteredLogs
    @FromDate       datetime2,
    @ToDate         datetime2,
    @SearchText     nvarchar(100) = NULL,
    @LogTypeIds     Ids READONLY,
    @AreaIds        Ids READONLY,
    @SubTypeIds     Ids READONLY,
    @UnitIds        Ids READONLY,
    @SortField      nvarchar(25) = NULL,
    @SortDirection  nvarchar(5) = NULL
AS
    SELECT *
    FROM LogsView
    WHERE   
        (CreatedDate >= @FromDate AND CreatedDate <= @ToDate) 
        AND ((([Text] LIKE '%' + @SearchText + '%' OR @SearchText IS NULL)
              AND (LogTypeId IN (SELECT Id FROM @LogTypeIds) OR NOT EXISTS (SELECT 1 FROM @LogTypeIds))
              AND (OperationAreaId IN (SELECT Id FROM @AreaIds) OR NOT EXISTS (SELECT 1 FROM @AreaIds))
              AND (Subtype IN (SELECT Id FROM @SubTypeIds) OR NOT EXISTS (SELECT 1 FROM @SubTypeIds))
              AND (Unit IN (SELECT Id FROM @UnitIds) OR NOT EXISTS (SELECT 1 FROM @UnitIds))) OR IsCritical = 1)    
ORDER BY
    CASE @SortField
        WHEN 'LogTypeId' THEN CreatedDate DESC(should be passed as argument), LogTypeId DESC
        ELSE CreatedDate DESC
    END

GO

你的思路完全可以实现，问题出在CASE表达式的用法上：CASE只能返回单个值，无法直接返回多个排序字段+方向的组合。下面提供两种符合你需求的修正方案：

方案1：静态SQL拆分CASE表达式（适合固定排序字段场景） #

把每个排序字段的判断和方向控制拆分成独立的CASE语句，避免单个CASE返回多值的语法错误：

CREATE PROCEDURE GetFilteredLogs
    @FromDate       datetime2,
    @ToDate         datetime2,
    @SearchText     nvarchar(100) = NULL,
    @LogTypeIds     Ids READONLY,
    @AreaIds        Ids READONLY,
    @SubTypeIds     Ids READONLY,
    @UnitIds        Ids READONLY,
    @SortField      nvarchar(25) = NULL,
    @SortDirection  nvarchar(5) = NULL
AS
    SELECT *
    FROM LogsView
    WHERE   
        (CreatedDate >= @FromDate AND CreatedDate <= @ToDate) 
        AND ((([Text] LIKE '%' + @SearchText + '%' OR @SearchText IS NULL)
              AND (LogTypeId IN (SELECT Id FROM @LogTypeIds) OR NOT EXISTS (SELECT 1 FROM @LogTypeIds))
              AND (OperationAreaId IN (SELECT Id FROM @AreaIds) OR NOT EXISTS (SELECT 1 FROM @AreaIds))
              AND (Subtype IN (SELECT Id FROM @SubTypeIds) OR NOT EXISTS (SELECT 1 FROM @SubTypeIds))
              AND (Unit IN (SELECT Id FROM @UnitIds) OR NOT EXISTS (SELECT 1 FROM @UnitIds))) OR IsCritical = 1)    
ORDER BY
    -- 当排序字段为LogTypeId时，优先按该字段排序，方向由参数控制
    CASE WHEN @SortField = 'LogTypeId' THEN LogTypeId END 
        CASE WHEN @SortDirection = 'ASC' THEN ASC ELSE DESC END,
    -- 默认排序：CreatedDate DESC，也可根据参数调整
    CreatedDate DESC;
GO

如果需要让默认排序的方向也支持参数化，再增加一组CASE语句即可。

方案2：动态SQL（适合需扩展多排序字段场景） #

如果未来要支持更多排序字段，动态SQL更灵活，直接拼接合法的ORDER BY语句即可，注意必须加白名单验证防止SQL注入：

CREATE PROCEDURE GetFilteredLogs
    @FromDate       datetime2,
    @ToDate         datetime2,
    @SearchText     nvarchar(100) = NULL,
    @LogTypeIds     Ids READONLY,
    @AreaIds        Ids READONLY,
    @SubTypeIds     Ids READONLY,
    @UnitIds        Ids READONLY,
    @SortField      nvarchar(25) = NULL,
    @SortDirection  nvarchar(5) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    -- 初始化默认排序规则
    DECLARE @OrderByClause NVARCHAR(MAX) = 'CreatedDate DESC';
    
    -- 验证排序字段和方向的合法性，仅允许白名单内的字段和方向
    IF @SortField IS NOT NULL AND @SortDirection IS NOT NULL
    BEGIN
        IF @SortField IN ('LogTypeId', 'CreatedDate', 'OperationAreaId')
        BEGIN
            SET @SortDirection = CASE WHEN @SortDirection IN ('ASC', 'DESC') THEN @SortDirection ELSE 'DESC' END;
            -- 按需求拼接多字段排序
            IF @SortField = 'LogTypeId'
                SET @OrderByClause = 'LogTypeId ' + @SortDirection + ', CreatedDate ' + @SortDirection;
            ELSE
                SET @OrderByClause = @SortField + ' ' + @SortDirection + ', CreatedDate DESC';
        END
    END
    
    -- 拼接完整SQL语句
    DECLARE @Sql NVARCHAR(MAX) = N'
    SELECT *
    FROM LogsView
    WHERE   
        (CreatedDate >= @FromDate AND CreatedDate <= @ToDate) 
        AND ((([Text] LIKE ''%'' + @SearchText + ''%'' OR @SearchText IS NULL)
              AND (LogTypeId IN (SELECT Id FROM @LogTypeIds) OR NOT EXISTS (SELECT 1 FROM @LogTypeIds))
              AND (OperationAreaId IN (SELECT Id FROM @AreaIds) OR NOT EXISTS (SELECT 1 FROM @AreaIds))
              AND (Subtype IN (SELECT Id FROM @SubTypeIds) OR NOT EXISTS (SELECT 1 FROM @SubTypeIds))
              AND (Unit IN (SELECT Id FROM @UnitIds) OR NOT EXISTS (SELECT 1 FROM @UnitIds))) OR IsCritical = 1)    
    ORDER BY ' + @OrderByClause;
    
    -- 执行动态SQL并传递参数
    EXEC sp_executesql @Sql,
        N'@FromDate datetime2, @ToDate datetime2, @SearchText nvarchar(100), @LogTypeIds Ids READONLY, @AreaIds Ids READONLY, @SubTypeIds Ids READONLY, @UnitIds Ids READONLY',
        @FromDate, @ToDate, @SearchText, @LogTypeIds, @AreaIds, @SubTypeIds, @UnitIds;
END
GO

内容的提问来源于stack exchange，提问作者Joshua