如何向存储过程传递变量，匹配CustomerID更新另一数据库对应行

实现方案 #

SQL是面向集合操作的语言，不需要像PHP那样循环遍历，直接用关联更新就能实现批量匹配更新，性能远高于逐行循环的方案，优先推荐如下写法：

CREATE PROCEDURE dbo.bhshSample
AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE t1
    SET t1.ptall = t2.nsma1_ans
    FROM table1 t1
    INNER JOIN (
        SELECT 
            CustomerID,
            nsma1_ans,
            ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY nsma1_tm DESC) AS row_num
        FROM table2
        WHERE nsma1_code = 'ptall'
    ) t2 ON t1.CustomerID = t2.CustomerID AND t2.row_num = 1
END

逻辑说明

• 子查询内使用开窗函数ROW_NUMBER()，按CustomerID分组，每组内按nsma1_tm倒序排序，同一个客户最新的那条记录行号为1
• 关联table1时仅取行号为1的记录，就实现了每个匹配的客户取最新ptall值批量更新的需求
• 若需要将table2中无匹配记录的客户ptall字段设为NULL，将INNER JOIN改为LEFT JOIN即可

可选：存储过程循环遍历实现（仅作学习参考，生产环境不推荐，大数据量下性能极低） #

如果你需要学习存储过程内的循环写法，可以参考游标实现：

CREATE PROCEDURE dbo.bhshSample_Loop
AS 
BEGIN
    SET NOCOUNT ON;
    -- 声明变量，类型需和实际表字段一致
    DECLARE @CurrCustomerID VARCHAR(64);
    DECLARE @CurrPtall NVARCHAR(255);

    -- 定义游标：取table2中所有去重的CustomerID
    DECLARE cur_customer CURSOR FOR
    SELECT DISTINCT CustomerID FROM table2;

    OPEN cur_customer;
    FETCH NEXT FROM cur_customer INTO @CurrCustomerID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 取当前客户最新的ptall值
        SELECT TOP 1 @CurrPtall = nsma1_ans
        FROM table2
        WHERE CustomerID = @CurrCustomerID AND nsma1_code = 'ptall'
        ORDER BY nsma1_tm DESC;

        -- 更新对应客户数据
        UPDATE table1 
        SET ptall = @CurrPtall
        WHERE CustomerID = @CurrCustomerID;

        FETCH NEXT FROM cur_customer INTO @CurrCustomerID;
    END

    -- 释放游标资源
    CLOSE cur_customer;
    DEALLOCATE cur_customer;
END

内容的提问来源于stack exchange，提问作者Kathy Kaiser Minnick