Question 112864 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search