skip to Main Content

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.

2

Answers


  1. 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. 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