SQL compilation error: error line 4 at position 21 Bind variable :Product_Id not set

Question

I am trying to bind a variable inside a with clause. But getting

Compilation Error. :Product_Id & :tmp_date

bind variables are not working but when I hardcoded them with '1' & current_date() then Proc got executed successfully.

CALL ent.p_Accounts('1', current_date())

    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=: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';```
        
    record := (execute immediate :query);
    return table(record);

Answer 1

You've embedded a variable name in the query assignment at a point in time where you already have the variable contents. You should use the contents of the variable in the assignment instead as follows:

query:= '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 ...

Otherwise define the query in the declare section as a default value, and place ? in there as placeholders for variables and then execute with a USING clause. See the following reference that shows EXECUTE IMMEDIATE using bind parameters.

https://docs.snowflake.com/en/sql-reference/sql/execute-immediate.html