CODEWITHSUNDEEP
snowflake-cloud-data-platform
mikelowry
mikelowry

Reputation: 1727

Stored procedure handling multiple SQL statements in Snowflake

I'm creating a stored procedure in Snowflake that will eventually be called by a task.

However I'm getting the following error:

Multiple SQL statements in a single API call are not supported; use one API call per statement instead

And not sure how approach the advised solution within my Javascript implementation.

Here's what I have

CREATE OR REPLACE PROCEDURE myStoreProcName()
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
var rs = snowflake.execute( { sqlText: 
`set curr_date = '2015-01-01'; 


CREATE OR REPLACE TABLE myTableName AS 


with cte1 as (
SELECT
  * 
FROM Table1
where date = $curr_date
)
,cte2 as (
SELECT
  * 
FROM Table2
where date = $curr_date
)

select * from
cte1 as 1 
inner join cte2 as 2
on(1.key = 2.key)

`
  
   } );
return 'Done.';
$$;

Upvotes: 0

Views: 4313

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

You could write your own helper function(idea of user: waldente):

this.executeMany=(s) => s.split(';').map(sqlText => snowflake.createStatement({sqlText}).execute());

executeMany('set curr_date = '2015-01-01'; 
             CREATE OR REPLACE TABLE ...');

The last statement should not contain ; it also may fail if there is ; in one of DDL which was not intended as separator.

Upvotes: 1

Felipe Hoffa
Felipe Hoffa

Reputation: 59315

You can't have:

var rs = snowflake.execute( { sqlText: 
`set curr_date = '2015-01-01'; 


CREATE OR REPLACE TABLE myTableName AS 
...
`

Instead you need to call execute twice (or more). Each for a different query ending in ;.

Upvotes: 0

Related Questions