Using Begin End to compile multiple queries into one statement, but how do return the result?-CodePudding

Below is the example.

BEGIN

set tablenameA = 'tablenamelala';

SELECT * FROM table($tablenameA);

END

I am expecting it will return me the result from the select statement.

CodePudding user response：

as per your requirement you can use the UDTF. which will return the table content as result. https://docs.snowflake.com/en/developer-guide/udf/sql/udf-sql-tabular-functions.html

CodePudding user response：

Variables work a little differently in an anonymous block. The code below will only work "as-is" in the Snowsight web-UI. Otherwise, it needs to be enclosed with double dollar signs.

Snowsight:

BEGIN

let tablenameA varchar := 'tablenameA';
let query varchar := 'SELECT * FROM '|| :tablenameA ||'';
let res resultset := (execute immediate :query);
return table(res);

END;

All Other clients:

execute immediate $$
BEGIN

let tablenameA varchar := 'tablenameA';
let query varchar := 'SELECT * FROM '|| :tablenameA ||'';
let res resultset := (execute immediate :query);
return table(res);

END;
$$;

if this is something repeated you can use a stored procedure.

create or replace procedure select_all(table_name varchar)
returns table()
language sql
execute as caller
as
declare
  res resultset;
  query varchar default 'select * from ' || :table_name || '';
begin
    res := (execute immediate :query);
    return table (res);
end;

-- call sproc
call select_all('MY_TABLE');