Generated string to SQL request in snowflake

Question

I am actually trying to find a solution for my issue. The problem is this one : A function generate a string, this string is a SQL request, and I want to use snowflake to "read" and execute this SQL request.

Do you have a solution for this kind of problem please ?

I still continue to try to find a solution if I find it I will put it here.

Here is my problem with more information about it.

create or replace function var_test(arg1 varchar)   
returns varchar   as   
$$     
'CREATE OR REPLACE TABLE ENV_EUT.EUT.TABLE_TEST_ALEXIS_' || arg1 || '(a varchar);'   
$$   
;  

SELECT var_test('3') AS num_table;

With this request, i get back a table with 1 column and a value in this column :

CREATE OR REPLACE TABLE ENV_EUT.EUT.TABLE_TEST_ALEXIS_3(a varchar);

My problem now is I don't succeed to execute the string in this table. Do you see a way to do this please ? Best regards Thank you all

How do you want to send this string as SQL to Snowflake? Programmatically via a connector? — Sergiu
– Sergiu, Commented Apr 14, 2022 at 12:47
This string is generated by a function, and I want to take this string in order to execute it in snowflake. — Alex
– Alex, Commented Apr 15, 2022 at 7:19

Tom Meacham · Accepted Answer · 2022-04-15 12:36:26Z

1

Check out Snowflake Scripting.

https://docs.snowflake.com/en/developer-guide/snowflake-scripting/index.html

You can declare a statement as a variable and execute it.

See also: execute immediate

https://docs.snowflake.com/en/sql-reference/sql/execute-immediate.html

-- very simple sproc    
create or replace procedure myprocedure(arg1 string)
  returns varchar
  language sql
  as
  $$
    -- declare variables
    declare
      smt string;
      
    begin
      -- construct statement
      smt := 'CREATE OR REPLACE TABLE TEST_ALEXIS_' || arg1 || ' (a varchar)';
      
      -- execute statement
      execute immediate smt;
      
      -- message to return on success
      return 'Successfully executed statement: ' || smt;
      
      -- message to return on exception
      exception
      when statement_error then
      return object_construct('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
    end;
  $$
  ;

-- call sproc to create table
call myprocedure('TEST');

edited Apr 15, 2022 at 12:36

answered Apr 14, 2022 at 13:20

Tom Meacham

1916 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Over a year ago

Thank you for these informations ! Here is my problem with more information about it.

create or replace function var_test(arg1 varchar)   returns varchar   as   $$     'CREATE OR REPLACE TABLE ENV_EUT.EUT.TABLE_TEST_ALEXIS_' || arg1 || '(a varchar);'   $$   ;       SELECT var_test('3') AS num_table;

With this request, i get back a table with 1 column and a value in this column : CREATE OR REPLACE TABLE ENV_EUT.EUT.TABLE_TEST_ALEXIS_3(a varchar); My problem now is I don't succed to execute the string in this table. Do you see a way to do this please ? Best regards

Tom Meacham Over a year ago

@Alex I edited my answer above with some sample code using a Snowflake Scripting to make a stored procedure.

Collectives™ on Stack Overflow

Generated string to SQL request in snowflake

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related