CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
Data Engineer
Data Engineer

Reputation: 67

Passing Variable to Snowflake Procedure

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

Upvotes: 2

Views: 1206

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

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}`
$$

Upvotes: 2

Related Questions