Snowflake SQL stored procedure not working

Question

I am trying to create a SQL stored procedure in Snowflake (Snowsight), but it is throwing run-time error. Not able to identify what the issue is.

I am getting thius error :

invalid identifier 'C_KEY_REC.KEY_NM'

Can anyone please help? I am new to this and will have to use the SQL stored procedure. Did not get any resolutions from around Google.

Below is a mocked-up version of what I have :

CREATE OR REPLACE TABLE KEY_TBL
(
     key_nm         VARCHAR
    ,key_val        VARCHAR
    ,active_yn      VARCHAR
);

INSERT INTO KEY_TBL(key_nm, key_val, active_yn )
VALUES
     ('KEY_NAME_1','KEY_VAL_1', 'Y')
    ,('KEY_NAME_2','KEY_VAL_2', 'Y')
;

CREATE OR REPLACE TABLE LOOP_CHK
(
     key_nm         VARCHAR
    ,key_val        VARCHAR
);

CREATE OR REPLACE PROCEDURE SP_LOOP_TEST()
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
    cur_key    CURSOR FOR SELECT key_nm, key_val FROM KEY_TBL WHERE active_yn = 'Y' ORDER BY key_nm;

BEGIN
    OPEN cur_key;
    FOR c_key_rec IN cur_key
    DO
        INSERT INTO LOOP_CHK(key_nm, key_val)
        VALUES(c_key_rec.key_nm, c_key_rec.key_val);
    END FOR;
    RETURN '1';
END;
;

CALL SP_LOOP_TEST();

I tried both with and without the OPEN statement. Checked the Snowflake docs and also this site and others in google. But could not resolve.

Answer 1

you need to use dynamic SQL (execute immediate) when using cursor

CREATE OR REPLACE PROCEDURE SP_LOOP_TEST()
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
    cur_key cursor for  SELECT key_nm, key_val 
    FROM KEY_TBL WHERE active_yn = 'Y' ORDER BY key_nm;
v_insert_stmt varchar2;
    --cur_main cursor for select sis_table,census_table, sis_column_list, process_flag 
     --       from CENSUS_HISTORY.CENSUS_SIS_MAPPING   WHERE process_flag = 'Y';
BEGIN
   -- OPEN cur_key;
   FOR c_key_rec IN cur_key do

   v_insert_stmt := ' INSERT INTO LOOP_CHK(key_nm, key_val) ' ||
        ' VALUES(' || '\'' || c_key_rec.key_nm || '\'' || ', 
                    ' || '\''||  c_key_rec.key_val || '\'' || ' )';
    execute immediate v_insert_stmt;    
    END FOR;
    RETURN '1';
END;