CODEWITHSUNDEEP
sqlstringsnowflake-cloud-data-platform
Alex
Alex

Reputation: 35

Generated string to SQL request in snowflake

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

Upvotes: 0

Views: 491

Answers (1)

Tom Meacham
Tom Meacham

Reputation: 191

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');

Upvotes: 1

Related Questions