How to call a stored procedure inside another stored procedure in snowflake

Question

I have a stored procedure SP1 which has the signature SP1("TABLE_NAME") And SP1 creates a table with the parameter passed

My second stored procedure has the signature SP2("PROCEDURE_NAME","PROCEDURE_PARAMETERS") below is a snippet of the code inside SP2

v_sqlCode = `CALL ` + PROCEDURE_NAME + `(` + P_PROCEDURE_PARAMETERS + `)`;

try{
var sqlStmt = snowflake.createStatement({sqlText : v_sqlCode});
var sqlRS = sqlSt,t.execute();
}

Unfortunately I keep getting the error invalid identifier for the P_PROCEDURE_PARAMETER

So if I did CALL SP2('SP1','C')

snowflake would say invalid identifier 'C' any ideas?

When you go to the Snowflake History tab, what does the SQL look like that the SP is trying to execute when it's trying to call the other SP? — Greg Pavlik

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2021-09-27T19:16:38

Snowflake would say invalid identifier 'C' any ideas?

The case is simple:

v_sqlCode = `CALL ` + PROCEDURE_NAME + `(` + P_PROCEDURE_PARAMETERS + `)`;

Call of: CALL SP2('SP1','C') becomes: CALL SP1(C) instead of CALL SP1('C'). Here C is an identifier and I guess you expect it to be a string literal.

Passing parameters as string is potentially dangerous as it is prone to SQL Injection.

Call using dollar-quoting:

CALL SP2('SP1',$$'C'$$)

How to call a stored procedure inside another stored procedure in snowflake

1 Answers