SQL Server更新存储过程优化：实现单字段独立更新

你的现有存储过程存在两个问题：一是当参数为NULL时会生成语法错误的UPDATE语句（比如UPDATE Pessoa SET WHERE ...），二是分两次UPDATE效率低下。可以通过CASE表达式实现单字段更新，也有更简洁的非动态SQL方案，以下是具体实现：

方案一：非动态SQL（推荐） #

直接用CASE判断参数是否有效，一次UPDATE完成所有需要的字段更新，无需拼接动态SQL，更安全简洁：

CREATE PROCEDURE sp_altera_pessoa 
    @Nome VARCHAR(15), 
    @Sobrenome VARCHAR(15) = NULL, 
    @CPF CHAR(11) = NULL
AS
BEGIN TRY
    BEGIN TRANSACTION

    UPDATE Pessoa
    SET Sobrenome_p = CASE WHEN @Sobrenome IS NOT NULL THEN @Sobrenome ELSE Sobrenome_p END,
        CPF = CASE WHEN @CPF IS NOT NULL THEN @CPF ELSE CPF END
    WHERE Nome_p = @Nome

    IF @@ERROR = 0
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
    THROW;
END CATCH

逻辑说明：当传入的参数不为NULL时，就将对应字段更新为参数值；如果参数为NULL，就保持字段原有值。不管是更新单个字段还是多个字段，都能一次完成，避免无效的SQL语句和多次表扫描。

方案二：优化后的动态SQL（若必须使用动态SQL） #

如果业务场景需要动态生成SQL，需确保拼接出有效的SET子句，避免空更新语句：

CREATE PROCEDURE sp_altera_pessoa 
    @Nome VARCHAR(15), 
    @Sobrenome VARCHAR(15) = NULL, 
    @CPF CHAR(11) = NULL
AS
BEGIN TRY
    BEGIN TRANSACTION
    DECLARE @Command NVARCHAR(MAX) = 'UPDATE Pessoa SET '
    DECLARE @SetClauses NVARCHAR(MAX) = ''

    -- 拼接需要更新的字段语句
    IF @Sobrenome IS NOT NULL
        SET @SetClauses += 'Sobrenome_p = @Sobrenome, '
    IF @CPF IS NOT NULL
        SET @SetClauses += 'CPF = @CPF, '

    -- 仅当有需要更新的字段时执行SQL
    IF LEN(@SetClauses) > 0
    BEGIN
        -- 去掉最后多余的逗号和空格
        SET @Command += LEFT(@SetClauses, LEN(@SetClauses) - 2) + ' WHERE Nome_p = @Nome'
        PRINT @Command
        EXEC sp_executesql 
            @Command,
            N'@Nome VARCHAR(15), @Sobrenome VARCHAR(15), @CPF CHAR(11)',
            @Nome, @Sobrenome, @CPF
    END

    IF @@ERROR = 0
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
    THROW;
END CATCH

逻辑说明：先判断每个参数是否有效，只拼接需要更新的字段，最后去除多余的逗号，确保生成的SQL语法正确，且只执行一次UPDATE操作。

内容的提问来源于stack exchange，提问作者Lucas César