Synapse中父子层级数据存储过程逻辑修复需求

由于Synapse不支持递归查询，需通过存储过程实现以下需求：为PRODUCT表中每个节点，找到其最终的Flag='Y'父节点，将该父节点的Zone值作为当前节点的region字段。

输入数据表（PRODUCT） #

期望输出 #

原存储过程的问题 #

1. 语法错误：INSERT INTO #x语句多了左括号，且WHERE子句后分号位置错误；
2. 字符错误：使用了中文单引号‘Y’、’N’，需替换为英文单引号；
3. 循环逻辑错误：删除#x后再查询其数据，此时临时表为空，无法获取下一层子节点；
4. 未处理Flag='Y'节点：这类节点的region应直接设为自身Zone；
5. 多层嵌套处理失效：原逻辑无法迭代处理像1→5→11这样的两层嵌套节点。

修复后的存储过程 #

CREATE PROCEDURE UPDATEHIERARCHIES 
AS
BEGIN
    -- 临时表存储当前层级节点及对应的最终region值
    CREATE TABLE #x (
        product_identifier NVARCHAR(200),
        region NVARCHAR(1)
    );

    -- 初始化：将Flag='Y'的节点加入临时表，region设为自身Zone
    INSERT INTO #x (product_identifier, region)
    SELECT product_identifier, Zone
    FROM PRODUCT
    WHERE Flag = 'Y';

    -- 更新Flag='Y'节点的region字段
    UPDATE p
    SET p.region = x.region
    FROM PRODUCT p
    INNER JOIN #x x ON p.product_identifier = x.product_identifier;

    DECLARE @ROWCOUNT INT;
    SELECT @ROWCOUNT = COUNT(*) FROM #x;

    WHILE @ROWCOUNT > 0
    BEGIN
        -- 临时表存储下一层待处理节点
        CREATE TABLE #nextLevel (
            product_identifier NVARCHAR(200),
            region NVARCHAR(1)
        );

        -- 找到当前层级节点的直接子节点（未设置region的Flag='N'节点），继承region值
        INSERT INTO #nextLevel (product_identifier, region)
        SELECT c.product_identifier, x.region
        FROM PRODUCT c
        INNER JOIN #x x ON c.parent_product_identifier = x.product_identifier
        WHERE c.Flag = 'N' AND c.region IS NULL;

        -- 更新子节点的region字段
        UPDATE c
        SET c.region = nl.region
        FROM PRODUCT c
        INNER JOIN #nextLevel nl ON c.product_identifier = nl.product_identifier;

        -- 替换临时表为下一层节点，准备迭代
        DROP TABLE #x;
        EXEC sp_rename '#nextLevel', '#x';

        -- 更新记录数，判断是否继续循环
        SELECT @ROWCOUNT = COUNT(*) FROM #x;
    END;

    -- 清理临时表
    IF OBJECT_ID('tempdb..#x') IS NOT NULL
        DROP TABLE #x;
END;

修复关键点说明 #

• 临时表结构优化：只保留product_identifier和region，简化数据传递逻辑；
• 初始化逻辑完善：先处理Flag='Y'的节点，确保其region正确设置，并作为迭代的起始层级；
• 迭代逻辑修正：使用新临时表存储下一层节点，避免原逻辑中删除后查询空表的错误，实现逐层向下更新子节点；
• 语法与字符修正：替换中文单引号，修正INSERT语句的语法问题；
• 避免重复更新：更新时仅处理未设置region的节点，提升效率。

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