开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜