snowflake create stored procedure to insert data from a table by parametrizing column names

user_8340881 0
Viewed 323

I have a stored procedure written as below. I want to parametrize the column names so that It can be reusable.

CREATE OR REPLACE PROCEDURE NEW() 
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$      
        let sql_command = `insert into TABLE_NEW
                            (ADR_LN_1,ADR_LN_2)
                          select 
                            ADR_LN_1,ADR_LN_2
                          from TABLE_OLD`;
        snowflake.execute({sqlText: sql_command});

    return 'success';
    $$;
    
call NEW();

I tried below query but it doesn't work

CREATE OR REPLACE PROCEDURE NEW(COL1 VARCHAR, COL2 VARCHAR) 
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$      
        let sql_command = `insert into TABLE_NEW
                            (COL1,COL2)
                          select 
                            COL1,COL2
                          from TABLE_OLD`;
        snowflake.execute({sqlText: sql_command});

    return 'success';
    $$;
    
call NEW('ADR_LN_1','ADR_LN_2');
snowflake-cloud-data-platform stored-procedures
1 Answers

You are mising ${} to get the JS string templates to work:

CREATE OR REPLACE PROCEDURE NEW(COL1 VARCHAR, COL2 VARCHAR) 
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$      
        let sql_command = `insert into TABLE_NEW
                            (${COL1},${COL2})
                          select 
                            ${COL1},${COL2}
                          from TABLE_OLD`;
        snowflake.execute({sqlText: sql_command});

    return 'success';
    $$;
    
call NEW('ADR_LN_1','ADR_LN_2');
user_1324380
Related