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

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

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
@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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related