write multipe statments in snowflake
Hi i have one doubt in snowflake how to write multiple update stments using stored procedure.
i have tried like below
create or replace procedure sp_multipleupdate()
returns table()
lANGUAGE sql
as
$$
declare res rsultset(
update TEST.PUBLIC.DEPT set Dname='PM' where deptid=10;
update TEST.PUBLIC.emp set name='veavi' where deptno=20;
update TEST.PUBLIC.loc set locname='del' where id=5;
)
begin
return table(res);
end;
$$;
getting error :
000006 (0A000): Multiple SQL statements in a single API call are not supported; use one API call per statement instead.开发者_JAVA技巧
Syntax error: unexpected '('. (line 2)
please let me know how to write query to achive this task in snowflake server .
Multiple SQL statements inside the resultset are not supported.
Rather than writing the UPDATE statements like that I would create a more generic procedure and pass arguments to it, so maybe split the above one in 3 procedures since these UPDATE statements are for different tables.
Here is a sample of a generic stored procedure:
create or replace procedure find_invoice_by_id_via_execute_immediate(id varchar)
returns table (id integer, price number(12,2))
language sql
as
declare
select_statement varchar;
res resultset;
begin
select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
res := (execute immediate :select_statement);
return table(res);
end;
You can read more here.
精彩评论