Inserting parameters of stored procedure into a table using Merge in snowflake

Tags: , , ,



I am trying to insert the parameter values of a stored procedure into the table using merge function in sql. The parameters consists of DB and schema name. I have written a stored procedure for that but, I don’t understand where I’m doing wrong. Here is my attempt:

CREATE TABLE TABL(DBName VARCHAR, SCName VARCHAR) // creating table

REATE OR REPLACE PROCEDURE repo(DB VARCHAR,SC VARCHAR) //need to push DB, SC INTO TABL
    RETURNS type
    LANGUAGE JAVASCRIPT
    AS
    $$      
       //Inserting parameters into table as values but didn;t work  
        var sql_command = "merge TABL as t using (SELECT +"DB"+ as database,+"SC"  as schema) as s on t.DBName = s.DB and t.SCName = s.schema when matched then update set t.DBName = t.DBName when not matched then insert (DBName, SCName) VALUES ('"+DB+"','"+SC +"')";
        snowflake.execute({sqlText: sql_command});

    return type;
    $$;

Answer

You can use binds:

CREATE TABLE TABL(DBName VARCHAR, SCName VARCHAR); // creating table

CREATE OR REPLACE PROCEDURE repo(DB VARCHAR,SC VARCHAR) 
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$      
        var sql_command = `merge into TABL as t 
                            using (SELECT :1 as database,:2  as schema) as s 
                            on t.DBName = s.database 
                            and t.SCName = s.schema 
                            when matched then update 
                            set t.DBName = t.DBName 
                            when not matched then insert 
                            (DBName, SCName) VALUES (:1,:2)`;
        snowflake.execute({sqlText: sql_command, binds: [DB, SC]});

    return 'success';
    $$;
    
    call repo('a', 'b');

See https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#binding-variables for more info.



Source: stackoverflow