使用雪花中的Merge将存储过程的参数插入到表中

Question

我正在尝试使用sql中的merge函数将存储过程的参数值插入到表中。参数由DB和模式名称组成。我已经为此编写了一个存储过程，但是，我不明白我哪里做错了。这是我的尝试：

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 1

您可以使用绑定：

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');

请参阅https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#binding-变量获取更多信息。