DDL statement in snowflake function

Question

I am trying to create a function in snowflake which have DDL statement inside it. Below is the function.

CREATE OR REPLACE function test_10252021(P1 VARCHAR(100)) returns
varchar not null language javascript as $$ try { `create or replace
temporary table temp_EMP
                as
                select * from demo_db.public.EMP
                where
                DEPT_NO='${P1}' `; return "Successful"; } catch(err) { return err.message; } $$ ;

Function is getting created but when I execute below select statement Temp table is not getting created.

Can anyone please help me how to execute ddl statements in function rather than a procedure.

select  test_10252021('20');
select * from  temp_EMP;

user_12756381 · Answer

You can't run SQL in a Snowflake JavaScript UDF, full stop. There's no workaround or kludge; there's just no way to do it.

If you want to use JavaScript to run DDL, you'll need to use a stored procedure.

CREATE OR REPLACE procedure test_10252021(P1 VARCHAR(100)) returns
varchar not null language javascript as 
$$ 
try { 

snowflake.execute({sqlText:`create or replace
temporary table temp_EMP
                as
                select * from demo_db.public.EMP
                where
                DEPT_NO='${P1}' `});
                
return "Successful"; 
} 
catch(err) { return err.message; } 
$$ ;

call test_10252021('20');

select * from temp_emp;

DDL statement in snowflake function

1 Answers