Passing Variable to Snowflake Procedure

Question

I am writing stored procedure in snowflake where i have several lines of select statements with multiple joins. So i need to use variable for each database schema. I tried experimenting with below code as an experiment but could not make it working. Can someone advise how to pass variable.

CREATE OR REPLACE PROCEDURE test_proc(tmpschema VARCHAR, dbschema VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
/* Load STARTED */
    try{
        snowflake.createStatement({sqlText:`TRUNCATE TABLE '${dbschema}'.TAB1`}).execute();
        snowflake.createStatement({sqlText:`TRUNCATE TABLE '${tmpschema}'.TAB2`}).execute();
        }
    catch(err){
        return 'Failed Truncating TMP tables: ' + err;
        }
$$
;

Procedure is being created successfully with above code, however calling is throwing an error.

call BIGDATA.test_proc('TMP', 'DB');
Error: Failed Truncating TMP tables:: ReferenceError: dbschema is not defined

score 2 · Accepted Answer · answered Oct 21 '21 at 22:04

You'll have to pay attention to the capitalization of variables when moving between SQL and JS.

This reproduces the problem:

CREATE OR REPLACE PROCEDURE test_proc(x VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
`${x}`
$$
;

call test_proc('')
;

-- 100132 (P0000): JavaScript execution error: Uncaught ReferenceError: x is not defined in TEST_PROC at '`${x}`' position 0

This fixes the problem (X):

CREATE OR REPLACE PROCEDURE test_proc(x VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
`${X}`
$$

This fixes it too ("x"):

CREATE OR REPLACE PROCEDURE test_proc("x" VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
`${x}`
$$

You beat me to it --- https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#case-sensitivity-in-javascript-arguments — patrick_at_snowflake, Oct 21 '21 at 22:06

Passing Variable to Snowflake Procedure

1 Answers1