存储过程WITH子句绑定变量编译错误求助

问题描述 #

在存储过程的WITH子句中使用:Product_Id和:tmpdate绑定变量时出现编译错误，但将变量硬编码为'1'和current_date()后，存储过程可正常执行。

错误原因 #

你当前的动态SQL写法存在两个问题：

1. 动态SQL字符串中的绑定变量未通过USING子句显式传递给execute immediate，导致Snowflake无法识别这些变量。
2. 本场景完全不需要使用动态SQL，静态SQL可直接引用存储过程的输入参数和局部变量，既简洁又避免绑定变量问题。

解决方案 #

方案1：使用静态SQL（推荐） #

直接将WITH子句作为静态SQL语句，直接引用存储过程的参数和变量，无需拼接动态字符串：

CREATE OR REPLACE PROCEDURE ent.p_Accounts(Product_Id VARCHAR(100), Date Date)
RETURNS table()
LANGUAGE SQL  
AS 
DECLARE
    tmpdate date;
BEGIN 
    tmpdate := Date;

    RETURN TABLE(
        WITH temp_Product 
        AS (
            select Product_EDW_Id From ent.PRODUCT
            Where PRODUCT_ID = :Product_Id
            AND PRODUCT_CLASS_NM IN ('Individual', 'Composite')
            UNION ALL
            SELECT R.Participating_Product_EDW_Id FROM ent.PRODUCT p
            INNER JOIN ent.Product_Group_Relationship R
            ON P.PRODUCT_EDW_ID = R.PRODUCT_EDW_ID
            WHERE p.product_id = :Product_Id
            AND :tmpdate BETWEEN R.START_DT AND nvl(R.END_DT, '2999-12-31')
        ),
        Latest_Custodian
        AS (
            SELECT 
                c.Src_Sys_Custodian_Account_Id, 
                c.client_edw_id, 
                c.Create_dt,
                ROW_NUMBER() OVER(PARTITION BY c.Src_Sys_Custodian_Account_Id, c.client_edw_id 
                                  ORDER BY c.Create_dt DESC) as RNUM1
            FROM ent.Product P
                INNER JOIN temp_Product TP
                ON P.Product_EDW_Id = TP.Product_EDW_Id
            Left Join ent.custodian C
            ON P.UDF7_TX = C.SRC_SYS_CUSTODIAN_ACCOUNT_ID AND 
                P.CLIENT_EDW_ID = C.CLIENT_EDW_ID
        )
        SELECT 
            P.Client_Product_Id AS account_code
            ,P.INCEPTION_DT AS tradable_date
            ,P.src_sys_close_dt AS close_date
            ,P.UDF31_TX AS account_type
            ,P.UDF15_TX AS trust_officer
            ,P.UDF16_TX AS trust_officer_city
            ,P.UDF17_TX AS trust_officer_phone
            ,P.UDF7_TX AS statement_account_number
            ,C1.custodian_mnemonic_nm AS custodian_code
            ,P.UDF8_TX AS custodian
            ,P.tax_exempt_fl AS taxable
            ,P.udf35_tx AS account_attribute
            ,P.PRODUCT_NM AS account_name
        from ent.PRODUCT P
        INNER JOIN temp_Product TP
        ON P.Product_EDW_Id = TP.Product_EDW_Id
        LEFT JOIN Latest_Custodian C
        ON P.UDF7_TX = C.SRC_SYS_CUSTODIAN_ACCOUNT_ID AND
            P.client_edw_id = C.client_edw_id AND
            RNUM1 = 1
        LEFT JOIN ent.CUSTODIAN C1
        ON C.SRC_SYS_CUSTODIAN_ACCOUNT_ID = C1.src_sys_custodian_account_id AND
        C.create_dt = C1.CREATE_DT
        ORDER BY P.product_structure_level_nm
    );
END;

方案2：若必须使用动态SQL，正确传递绑定参数 #

将动态字符串中的绑定变量替换为?占位符，通过USING子句按顺序传递对应的变量：

CREATE OR REPLACE PROCEDURE ent.p_Accounts(Product_Id VARCHAR(100), Date Date)
RETURNS table()
LANGUAGE SQL  
AS 
DECLARE
    tmpdate date;
    query varchar;
    record resultset;
BEGIN 
    tmpdate := Date;

query:= 'with temp_Product 
    AS (
        select Product_EDW_Id From ent.PRODUCT
    Where PRODUCT_ID=?
    AND PRODUCT_CLASS_NM IN (''Individual'', ''Composite'')
    UNION ALL
    SELECT R.Participating_Product_EDW_Id FROM ent.PRODUCT p
    INNER JOIN ent.Product_Group_Relationship R
    ON P.PRODUCT_EDW_ID = R.PRODUCT_EDW_ID
    WHERE p.product_id =?
    AND ? BETWEEN R.START_DT AND nvl(R.END_DT, ''2999-12-31'')
    ),
 Latest_Custodian
    AS (
    SELECT 
        c.Src_Sys_Custodian_Account_Id, 
        c.client_edw_id, 
        c.Create_dt,
        ROW_NUMBER() OVER(PARTITION BY c.Src_Sys_Custodian_Account_Id, c.client_edw_id 
                          ORDER BY c.Create_dt DESC) as RNUM1
    FROM ent.Product P
        INNER JOIN temp_Product TP
        ON P.Product_EDW_Id = TP.Product_EDW_Id
    Left Join ent.custodian C
    ON P.UDF7_TX = C.SRC_SYS_CUSTODIAN_ACCOUNT_ID AND 
        P.CLIENT_EDW_ID = C.CLIENT_EDW_ID
    )
    SELECT 
        P.Client_Product_Id AS account_code
        ,P.INCEPTION_DT AS tradable_date
        ,P.src_sys_close_dt AS close_date
        ,P.UDF31_TX AS account_type
        ,P.UDF15_TX AS trust_officer
        ,P.UDF16_TX AS trust_officer_city
        ,P.UDF17_TX AS trust_officer_phone
        ,P.UDF7_TX AS statement_account_number
        ,C1.custodian_mnemonic_nm AS custodian_code
        ,P.UDF8_TX AS custodian
        ,P.tax_exempt_fl AS taxable
        ,P.udf35_tx AS account_attribute
        ,P.PRODUCT_NM AS account_name
    from ent.PRODUCT P
    INNER JOIN temp_Product TP
    ON P.Product_EDW_Id = TP.Product_EDW_Id
    LEFT JOIN Latest_Custodian C
    ON P.UDF7_TX = C.SRC_SYS_CUSTODIAN_ACCOUNT_ID AND
        P.client_edw_id = C.client_edw_id AND
        RNUM1 = 1
    LEFT JOIN ent.CUSTODIAN C1
    ON C.SRC_SYS_CUSTODIAN_ACCOUNT_ID = C1.src_sys_custodian_account_id AND
    C.create_dt = C1.CREATE_DT
    ORDER BY P.product_structure_level_nm';
        
    record := (execute immediate :query USING :Product_Id, :Product_Id, :tmpdate);
    return table(record);
END;

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