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:
JavaScript
x
14
14
1
CREATE TABLE TABL(DBName VARCHAR, SCName VARCHAR) // creating table
2
3
REATE OR REPLACE PROCEDURE repo(DB VARCHAR,SC VARCHAR) //need to push DB, SC INTO TABL
4
RETURNS type
5
LANGUAGE JAVASCRIPT
6
AS
7
$$
8
//Inserting parameters into table as values but didn;t work
9
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 +"')";
10
snowflake.execute({sqlText: sql_command});
11
12
return type;
13
$$;
14
Advertisement
Answer
You can use binds:
JavaScript
1
22
22
1
CREATE TABLE TABL(DBName VARCHAR, SCName VARCHAR); // creating table
2
3
CREATE OR REPLACE PROCEDURE repo(DB VARCHAR,SC VARCHAR)
4
RETURNS string
5
LANGUAGE JAVASCRIPT
6
AS
7
$$
8
var sql_command = `merge into TABL as t
9
using (SELECT :1 as database,:2 as schema) as s
10
on t.DBName = s.database
11
and t.SCName = s.schema
12
when matched then update
13
set t.DBName = t.DBName
14
when not matched then insert
15
(DBName, SCName) VALUES (:1,:2)`;
16
snowflake.execute({sqlText: sql_command, binds: [DB, SC]});
17
18
return 'success';
19
$$;
20
21
call repo('a', 'b');
22
See https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#binding-variables for more info.