在存储过程中基于不同ID执行两条Update语句是否为不良实践？

这种写法本身不算绝对的不良实践，但你的具体实现存在明显的可优化点，需要结合性能、一致性和维护性来评估：

1. 当前代码的核心问题 #

• 全表更新隐患：两条Update都没有加WHERE条件，会遍历整个TIPS表的所有行——哪怕只有几行需要修改。数据量稍大时，不仅会拖慢执行速度，还会造成不必要的锁表，影响其他业务操作。
• 逻辑冗余严重：两条Update的结构和逻辑高度重复，只是针对TipID 1-3和4-6的区分。后续新增TipID或修改逻辑时，需要同时改动两处代码，容易出现遗漏或不一致。
• 事务缺失风险：如果没有显式事务包裹，第一条Update执行成功但第二条失败的话，会直接导致数据不一致。

2. 如何界定是否为不良实践？ #

如果是基于业务需求的合理拆分（比如两组数据的更新逻辑完全独立，后续可能单独调整），两条Update本身没问题。但你的写法因为全表扫描和冗余代码，属于不够优化的实现，而非“不良实践”的范畴。如果能合并成一条更高效的Update，显然是更好的选择。

3. 优化方案推荐 #

方案1：合并为单条带条件的Update #

只更新需要修改的行，减少冗余逻辑，同时保证性能：

CREATE PROCEDURE [dbo].[UPD_Tip]
    @TipId INT, 
    @TipDlvId INT,
    @UpdatedBy nvarchar(60),     
    @TipVal1 DECIMAL(5,2) = NULL,
    @TipVal2 DECIMAL(5,2) = NULL,
    @TipVal3 DECIMAL(5,2) = NULL,
    @TipVal4 DECIMAL(5,2) = NULL,
    @TipVal5 DECIMAL(5,2) = NULL,
    @TipVal6 DECIMAL(5,2) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    UPDATE TIPS 
    SET 
        IsDefault = CASE
                        WHEN TipID IN (1,2,3) THEN
                            CASE 
                                WHEN @TipId = -1 THEN IsDefault 
                                WHEN @TipId = 0 THEN 0 
                                WHEN TipId = @TipId THEN 1  
                                ELSE 0 
                            END
                        WHEN TipID IN (4,5,6) THEN
                            CASE 
                                WHEN @TipDlvId = -1 THEN IsDefault 
                                WHEN @TipDlvId = 0 THEN 0 
                                WHEN TipId = @TipDlvId THEN 1  
                                ELSE 0 
                            END
                        ELSE IsDefault
                    END,
        DisplayName = CASE 
                          WHEN TipID = 1 AND @TipVal1 IS NOT NULL THEN CONCAT(CAST(@TipVal1 * 100 AS int), '%')
                          WHEN TipID = 2 AND @TipVal2 IS NOT NULL THEN CONCAT(CAST(@TipVal2 * 100 AS int), '%')
                          WHEN TipID = 3 AND @TipVal3 IS NOT NULL THEN CONCAT(CAST(@TipVal3 * 100 AS int), '%')
                          WHEN TipID = 4 AND @TipVal4 IS NOT NULL THEN CONCAT(CAST(@TipVal4 * 100 AS int), '%')
                          WHEN TipID = 5 AND @TipVal5 IS NOT NULL THEN CONCAT(CAST(@TipVal5 * 100 AS int), '%')
                          WHEN TipID = 6 AND @TipVal6 IS NOT NULL THEN CONCAT(CAST(@TipVal6 * 100 AS int), '%')
                          ELSE DisplayName
                      END,
        TipValue = CASE 
                        WHEN TipID = 1 AND @TipVal1 IS NOT NULL THEN CAST(@TipVal1 * 100 AS money)/100
                        WHEN TipID = 2 AND @TipVal2 IS NOT NULL THEN CAST(@TipVal2 * 100 AS money)/100
                        WHEN TipID = 3 AND @TipVal3 IS NOT NULL THEN CAST(@TipVal3 * 100 AS money)/100
                        WHEN TipID = 4 AND @TipVal4 IS NOT NULL THEN CAST(@TipVal4 * 100 AS money)/100
                        WHEN TipID = 5 AND @TipVal5 IS NOT NULL THEN CAST(@TipVal5 * 100 AS money)/100
                        WHEN TipID = 6 AND @TipVal6 IS NOT NULL THEN CAST(@TipVal6 * 100 AS money)/100
                        ELSE TipValue
                    END,
        LastUpdated = GETDATE(),
        UpdatedBy = @UpdatedBy
    WHERE 
        TipID IN (1,2,3,4,5,6)
        AND (
            (TipID IN (1,2,3) AND @TipId NOT IN (-1))
            OR (TipID IN (4,5,6) AND @TipDlvId NOT IN (-1))
            OR (TipID=1 AND @TipVal1 IS NOT NULL)
            OR (TipID=2 AND @TipVal2 IS NOT NULL)
            OR (TipID=3 AND @TipVal3 IS NOT NULL)
            OR (TipID=4 AND @TipVal4 IS NOT NULL)
            OR (TipID=5 AND @TipVal5 IS NOT NULL)
            OR (TipID=6 AND @TipVal6 IS NOT NULL)
        );

    COMMIT TRANSACTION;
END

方案2：保留两条Update但添加WHERE条件 #

如果业务上必须拆分两组更新，至少要加上WHERE条件限制范围，同时用事务保证一致性：

CREATE PROCEDURE [dbo].[UPD_Tip]
    @TipId INT, 
    @TipDlvId INT,
    @UpdatedBy nvarchar(60),     
    @TipVal1 DECIMAL(5,2) = NULL,
    @TipVal2 DECIMAL(5,2) = NULL,
    @TipVal3 DECIMAL(5,2) = NULL,
    @TipVal4 DECIMAL(5,2) = NULL,
    @TipVal5 DECIMAL(5,2) = NULL,
    @TipVal6 DECIMAL(5,2) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    -- 更新TipID 1-3的行
    UPDATE TIPS 
    SET IsDefault = (CASE 
                         WHEN @TipId = -1 THEN IsDefault 
                         WHEN @TipId = 0 THEN 0 
                         WHEN TipId = @TipId THEN 1  
                         ELSE 0 
                      END),
        DisplayName = CASE TipID 
                          WHEN 1 THEN 
                              CASE 
                                  WHEN @TipVal1 IS NOT NULL 
                                      THEN CONCAT(CAST(@TipVal1 * 100 AS int), '%') 
                                      ELSE DisplayName 
                              END
                          WHEN 2 THEN
                              CASE 
                                  WHEN @TipVal2 IS NOT NULL 
                                      THEN CONCAT(CAST(@TipVal2 * 100 as int), '%') 
                                      ELSE DisplayName 
                              END
                          WHEN 3 THEN
                              CASE 
                                  WHEN @TipVal3 IS NOT NULL 
                                      THEN CONCAT(CAST(@TipVal3 * 100 AS int), '%') 
                                      ELSE DisplayName 
                              END
                        END,
        TipValue = CASE TipID 
                        WHEN  1 THEN 
                            CASE WHEN @TipVal1 IS NOT NULL  THEN CAST(@TipVal1 * 100 AS money)/100 ELSE TipValue END
                        WHEN 2 THEN 
                            CASE WHEN @TipVal2 IS NOT NULL  THEN CAST(@TipVal2 * 100 AS money)/100 ELSE TipValue END 
                        WHEN 3 THEN 
                            CASE WHEN @TipVal3 IS NOT NULL then CAST(@TipVal3 * 100 AS money)/100 ELSE TipValue END
                    END,
        LastUpdated = GETDATE(),
        UpdatedBy = @UpdatedBy
    WHERE TipID IN (1,2,3)
        AND (
            @TipId NOT IN (-1)
            OR @TipVal1 IS NOT NULL
            OR @TipVal2 IS NOT NULL
            OR @TipVal3 IS NOT NULL
        );

    -- 更新TipID 4-6的行
    UPDATE TIPS 
        SET IsDefault =  (CASE 
                               WHEN @TipDlvId = -1 THEN IsDefault 
                               WHEN @TipDlvId = 0 THEN 0 
                               WHEN TipId = @TipDlvId THEN 1 ELSE 0 END 
                           ),
        DisplayName = CASE TipID 
                        WHEN 4 THEN 
                            CASE WHEN @TipVal4 IS NOT NULL THEN CONCAT(cast(@TipVal4 * 100 as int), '%') ELSE DisplayName END
                        WHEN 5 THEN
                            CASE WHEN @TipVal5 IS NOT NULL THEN CONCAT(cast(@TipVal5 * 100 as int), '%') ELSE DisplayName END
                        WHEN 6 THEN
                            CASE WHEN @TipVal6 IS NOT NULL THEN CONCAT(cast(@TipVal6 * 100 as int), '%') ELSE DisplayName END
                        END,
        TipValue = CASE TipID 
                        WHEN  4 THEN 
                            CASE WHEN @TipVal4 IS NOT NULL  THEN CAST(@TipVal4 * 100 AS money)/100 ELSE TipValue END
                        WHEN 5 THEN 
                            CASE WHEN @TipVal5 IS NOT NULL  THEN CAST(@TipVal5 * 100 AS money)/100 ELSE TipValue END 
                        WHEN 6 THEN 
                            CASE WHEN @TipVal6 IS NOT NULL then CAST(@TipVal6 * 100 AS money)/100 ELSE TipValue END
                    END,
        LastUpdated = GETDATE(),
        UpdatedBy = @UpdatedBy
    WHERE TipID IN (4,5,6)
        AND (
            @TipDlvId NOT IN (-1)
            OR @TipVal4 IS NOT NULL
            OR @TipVal5 IS NOT NULL
            OR @TipVal6 IS NOT NULL
        );

    COMMIT TRANSACTION;
END

关键注意事项 #

• 事务必须加：无论用哪种方案，都要用显式事务包裹更新逻辑，确保操作的原子性，避免数据不一致。
• 杜绝全表扫描：一定要通过WHERE条件限制更新的行范围，这是性能优化的核心。
• 减少重复代码：尽量合并冗余逻辑，降低后续维护的成本和出错概率。

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