如何在SQL存储过程中根据@ReportID实现条件GROUP BY？

当前存储过程中，当@ReportID = 212或@ReportID = 216时会执行插入临时表#tmprectype的操作，需求是：

• 当@ReportID = 212时，保留现有GROUP BY子句
• 当@ReportID = 216时，替换为另一组GROUP BY子句

原始代码片段：

IF (@ReportID = 212 OR @ReportID = 216)
BEGIN
    SET @ShowCodes = 1

    INSERT INTO #tmprectype (ownerid, priorbalance, charges, assessment, AccountNumber, AssocName, adjustment, deposit, [return], payment, [address], ownerName, creditCode)

    -- SELECT columns

    FROM vOwnerLedgerByChg2 l WITH(NOLOCK)
    INNER JOIN vOwnerPropertyAddress v WITH(NOLOCK) ON l.OwnerID = v.OwnerID
    INNER JOIN Association a WITH(NOLOCK) ON a.AssocID = l.AssocID
    LEFT OUTER JOIN AssociationCharge g WITH(NOLOCK) ON l.AssocChgID = g.AssocChgID
    LEFT OUTER JOIN vallglhistory h ON h.AssocID = l.AssocID AND h.GlAccountID = g.GLAccountID
    WHERE l.AssocID = @AssocID 
    AND LedgerDate <= @Process 
    AND ISNULL(g.ChgTypeID, 0) = 6
    AND (ISNULL(@AssocChgID,0) = 0 OR l.AssocChgID = @AssocChgID)

    -- GROUP BY should be different depending on the @ReportID

    GROUP BY a.assoccode, a.assocname, l.associd, l.ownerid, v.ownername, v.propaddress, v.propcity, v.propstate, v.propzip, l.BKID, l.assocchgid, g.descr, l.AssocChgID, h.Code
    ORDER BY CONVERT(varchar(8),a.assoccode) + CONVERT(varchar(16),l.ownerid), CASE WHEN ISNULL(l.BKID,0) > 0 THEN CASE WHEN l.assocchgid = -1 THEN 'Credit (Bankruptcy)' ELSE g.descr + ' (Bankruptcy)' END 
        ELSE CASE WHEN l.assocchgid = -1 THEN 'Credit' ELSE g.descr END END
END

方法一：拆分IF分支，分别编写逻辑 #

这是最直观且易维护的方案，将原有的IF条件拆分为两个独立分支，各自使用对应的GROUP BY：

IF (@ReportID = 212 OR @ReportID = 216)
BEGIN
    SET @ShowCodes = 1

    IF @ReportID = 212
    BEGIN
        INSERT INTO #tmprectype (ownerid, priorbalance, charges, assessment, AccountNumber, AssocName, adjustment, deposit, [return], payment, [address], ownerName, creditCode)
        -- SELECT columns（需确保聚合逻辑与GROUP BY匹配）
        FROM vOwnerLedgerByChg2 l WITH(NOLOCK)
        INNER JOIN vOwnerPropertyAddress v WITH(NOLOCK) ON l.OwnerID = v.OwnerID
        INNER JOIN Association a WITH(NOLOCK) ON a.AssocID = l.AssocID
        LEFT OUTER JOIN AssociationCharge g WITH(NOLOCK) ON l.AssocChgID = g.AssocChgID
        LEFT OUTER JOIN vallglhistory h ON h.AssocID = l.AssocID AND h.GlAccountID = g.GLAccountID
        WHERE l.AssocID = @AssocID 
        AND LedgerDate <= @Process 
        AND ISNULL(g.ChgTypeID, 0) = 6
        AND (ISNULL(@AssocChgID,0) = 0 OR l.AssocChgID = @AssocChgID)
        -- 212对应的GROUP BY
        GROUP BY a.assoccode, a.assocname, l.associd, l.ownerid, v.ownername, v.propaddress, v.propcity, v.propstate, v.propzip, l.BKID, l.assocchgid, g.descr, l.AssocChgID, h.Code
        ORDER BY CONVERT(varchar(8),a.assoccode) + CONVERT(varchar(16),l.ownerid), CASE WHEN ISNULL(l.BKID,0) > 0 THEN CASE WHEN l.assocchgid = -1 THEN 'Credit (Bankruptcy)' ELSE g.descr + ' (Bankruptcy)' END 
            ELSE CASE WHEN l.assocchgid = -1 THEN 'Credit' ELSE g.descr END END
    END
    ELSE IF @ReportID = 216
    BEGIN
        INSERT INTO #tmprectype (ownerid, priorbalance, charges, assessment, AccountNumber, AssocName, adjustment, deposit, [return], payment, [address], ownerName, creditCode)
        -- SELECT columns（需调整聚合逻辑以匹配新的GROUP BY）
        FROM vOwnerLedgerByChg2 l WITH(NOLOCK)
        INNER JOIN vOwnerPropertyAddress v WITH(NOLOCK) ON l.OwnerID = v.OwnerID
        INNER JOIN Association a WITH(NOLOCK) ON a.AssocID = l.AssocID
        LEFT OUTER JOIN AssociationCharge g WITH(NOLOCK) ON l.AssocChgID = g.AssocChgID
        LEFT OUTER JOIN vallglhistory h ON h.AssocID = l.AssocID AND h.GlAccountID = g.GLAccountID
        WHERE l.AssocID = @AssocID 
        AND LedgerDate <= @Process 
        AND ISNULL(g.ChgTypeID, 0) = 6
        AND (ISNULL(@AssocChgID,0) = 0 OR l.AssocChgID = @AssocChgID)
        -- 216对应的GROUP BY，替换为你的目标字段
        GROUP BY a.assoccode, a.assocname, l.associd, l.ownerid, v.ownername
        ORDER BY CONVERT(varchar(8),a.assoccode) + CONVERT(varchar(16),l.ownerid), CASE WHEN ISNULL(l.BKID,0) > 0 THEN CASE WHEN l.assocchgid = -1 THEN 'Credit (Bankruptcy)' ELSE g.descr + ' (Bankruptcy)' END 
            ELSE CASE WHEN l.assocchgid = -1 THEN 'Credit' ELSE g.descr END END
    END
END

优点：逻辑清晰，便于调试和维护，无动态SQL的潜在风险；缺点：存在部分代码重复。

方法二：使用动态SQL拼接GROUP BY #

若要减少代码重复，可通过动态SQL生成对应GROUP BY子句：

IF (@ReportID = 212 OR @ReportID = 216)
BEGIN
    SET @ShowCodes = 1

    DECLARE @GroupByClause NVARCHAR(MAX)
    DECLARE @Sql NVARCHAR(MAX)

    -- 根据ReportID设置GROUP BY子句
    SET @GroupByClause = CASE @ReportID
        WHEN 212 THEN 'a.assoccode, a.assocname, l.associd, l.ownerid, v.ownername, v.propaddress, v.propcity, v.propstate, v.propzip, l.BKID, l.assocchgid, g.descr, l.AssocChgID, h.Code'
        WHEN 216 THEN 'a.assoccode, a.assocname, l.associd, l.ownerid, v.ownername' -- 替换为你的目标GROUP BY字段
    END

    -- 拼接完整SQL语句
    SET @Sql = N'
        INSERT INTO #tmprectype (ownerid, priorbalance, charges, assessment, AccountNumber, AssocName, adjustment, deposit, [return], payment, [address], ownerName, creditCode)
        -- SELECT columns（需确保聚合逻辑与GROUP BY兼容）
        FROM vOwnerLedgerByChg2 l WITH(NOLOCK)
        INNER JOIN vOwnerPropertyAddress v WITH(NOLOCK) ON l.OwnerID = v.OwnerID
        INNER JOIN Association a WITH(NOLOCK) ON a.AssocID = l.AssocID
        LEFT OUTER JOIN AssociationCharge g WITH(NOLOCK) ON l.AssocChgID = g.AssocChgID
        LEFT OUTER JOIN vallglhistory h ON h.AssocID = l.AssocID AND h.GlAccountID = g.GLAccountID
        WHERE l.AssocID = @AssocID 
        AND LedgerDate <= @Process 
        AND ISNULL(g.ChgTypeID, 0) = 6
        AND (ISNULL(@AssocChgID,0) = 0 OR l.AssocChgID = @AssocChgID)
        GROUP BY ' + @GroupByClause + N'
        ORDER BY CONVERT(varchar(8),a.assoccode) + CONVERT(varchar(16),l.ownerid), CASE WHEN ISNULL(l.BKID,0) > 0 THEN CASE WHEN l.assocchgid = -1 THEN ''Credit (Bankruptcy)'' ELSE g.descr + '' (Bankruptcy)'' END 
            ELSE CASE WHEN l.assocchgid = -1 THEN ''Credit'' ELSE g.descr END END'

    -- 执行动态SQL，传递参数
    EXEC sp_executesql @Sql, 
        N'@AssocID INT, @Process DATETIME, @AssocChgID INT',
        @AssocID = @AssocID, 
        @Process = @Process, 
        @AssocChgID = @AssocChgID
END

注意事项：

• 动态SQL中的字符串单引号需用两个单引号转义（如''Credit''）
• 确保SELECT中的非聚合字段全部包含在对应GROUP BY中，否则会触发语法错误
• 若参数类型与示例不同，需调整sp_executesql的参数定义部分

优点：减少代码重复，适合后续新增更多ReportID分支的场景；缺点：可读性略差，调试难度稍高，需注意参数化以避免SQL注入风险（本示例已使用参数化查询）。

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