SQL存储过程迁移至Snowflake时EXEC语句替代方案咨询

Snowflake平台适配方案 #

原代码中的EXEC执行动态SQL的逻辑，在Snowflake中可以直接使用EXECUTE IMMEDIATE语法替代，完整适配后的存储过程逻辑如下：

CREATE OR REPLACE PROCEDURE MIGRATE_BR_PROC()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    Segment_choose VARCHAR(255) := 'All';
    SubSegment_choose VARCHAR(255) := 'All';
    BREAK INT := 0;
    SQL VARCHAR(8000);
    Segment VARCHAR(255);
    SubSegment1 VARCHAR(255);
    SubSegment2 VARCHAR(255);
    SubSegment3 VARCHAR(255);
    Area VARCHAR(255);
BEGIN
    WHILE (BREAK < 5) DO
        Segment := CASE
            WHEN BREAK = 4 THEN 'REPOSITORY'
            WHEN BREAK = 3 THEN 'Region'
            WHEN BREAK = 2 THEN 'Time_on_Book'
            WHEN BREAK = 1 THEN 'Sector'
        END;

        SubSegment1 := CASE
            WHEN BREAK = 4 THEN 'Region'
            WHEN BREAK = 3 THEN 'REPOSITORY'
            WHEN BREAK = 2 THEN 'REPOSITORY'
            WHEN BREAK = 1 THEN 'REPOSITORY'
        END;

        SubSegment2 := CASE
            WHEN BREAK = 4 THEN 'Time_on_Book'
            WHEN BREAK = 3 THEN 'Time_on_Book'
            WHEN BREAK = 2 THEN 'Region'
            WHEN BREAK = 1 THEN 'Region'
        END;

        SubSegment3 := CASE
            WHEN BREAK = 4 THEN 'Sector'
            WHEN BREAK = 3 THEN 'Sector'
            WHEN BREAK = 2 THEN 'Sector'
            WHEN BREAK = 1 THEN 'Time_on_Book'
        END;

        Area := CASE
            WHEN BREAK = 4 THEN 'REPOSITORY'
            WHEN BREAK = 3 THEN 'Region'
            WHEN BREAK = 2 THEN 'Time_on_Book'
            WHEN BREAK = 1 THEN 'Sector'
        END;

        SQL := '
        WITH CTEA AS 
        (
        SELECT DISTINCT Case_created_date
                ,IFNULL(BUSINESS_AREA,''All'') AS BUSINESS_AREA
                ,IFNULL('||Segment||',''Overall'') AS Segment
                ,CASE WHEN CONCAT('||SubSegment1||','||SubSegment2||','||SubSegment3||') = ''''
                       THEN ''Overall''
                       ELSE CONCAT('||SubSegment1||','||SubSegment2||','||SubSegment3||') 
                  END AS SubSegment
                 ,'''||Area||''' AS Area
                 ,COUNT(CASE WHEN case_modified_date = case_created_date THEN caseID ELSE NULL END) AS Cases_Generated      
                 ,SUM(manual_case) AS Manual_Case
                 ,SUM(model_only) AS Model_Case
                 ,SUM(rules_only) AS Rules_Case
                 ,SUM(rules_model) AS Both_Case
                 ,AVG(CASE WHEN model_Score <> 0 THEN model_score ELSE NULL END) AS Model_Score                                                                              
        FROM #BR_Master
        GROUP BY GROUPING SETS
              (
               (Case_created_date)                                  
              ,(Case_created_date,BUSINESS_AREA,'||Segment||') 
              ,(Case_created_date,BUSINESS_AREA,'||Segment||',REPOSITORY)
              ,(Case_created_date,BUSINESS_AREA,'||Segment||',Region)
              ,(Case_created_date,BUSINESS_AREA,'||Segment||',Time_on_Book)
              ,(Case_created_date,BUSINESS_AREA,'||Segment||',Sector)
              ,(Case_created_date,'||Segment||')
              ,(Case_created_date,'||Segment||',REPOSITORY)
              ,(Case_created_date,'||Segment||',Region)
              ,(Case_created_date,'||Segment||',Time_on_Book)
              ,(Case_created_date,'||Segment||',Sector)
              ,(Case_created_date,BUSINESS_AREA)
              ,(Case_created_date,REPOSITORY)
              ,(Case_created_date,REGION)
              ,(Case_created_date,TIME_ON_BOOK)
              ,(Case_created_date,SECTOR)
              )
        )
        INSERT INTO #BR_Template
        (
         Reporting_Month
        ,BUSINESS_AREA
        ,Segment
        ,SubSegment
        ,Area
        ,CasES_GENERATED
        ,manual_case
        ,model_case
        ,rules_case
        ,both_case
        ,model_score
        )
        SELECT Case_created_date
                ,BUSINESS_AREA
                ,Segment
                ,SubSegment
                ,Area
                ,CasES_GENERATED
                ,manual_case
                ,model_case
                ,rules_case
                ,both_case
                ,model_score
        FROM CTEA 
        ';

        -- 替换原EXEC语句的实现
        EXECUTE IMMEDIATE SQL;

        BREAK := BREAK + 1;
    END WHILE;
    RETURN 'PROCEDURE EXECUTED SUCCESSFULLY';
END;
$$;

补充说明

• 原T-SQL中的ISNULL函数在Snowflake中对应IFNULL，语法规则完全一致，已在代码中替换
• 临时表#BR_Master、#BR_Template在Snowflake中支持度和SQL Server一致，无需额外修改
• GROUPING SETS语法Snowflake原生兼容，不需要调整聚合逻辑
• 执行存储过程使用CALL MIGRATE_BR_PROC();即可
• 无需封装存储过程的场景，也可以直接使用EXECUTE IMMEDIATE '<拼接好的SQL语句>'语法单独执行动态SQL

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