[SOLVED] Execute SnowSQL command conditionally

Execute SnowSQL command conditionally

StackOverflow.

I have some batch jobs which run under SnowSQL in Linux. I'm trying to achieve the following:

If a condition is true, then execute a !system statement.

I have tried the following with no luck so far.

SFUSER#MYWAREHOUSE@MYDB.MYSCHEMA>SET SCRIPT =
                                  $$
                                  BEGIN
                                      LET count1 := 0;
                                      IF (count1 = 0) THEN
                                          !system 'echo asdfwerg > meow0111';
                                      END IF;

                                      RETURN 'MEOW';
                                  END;
                                  $$
                                  ;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.099s



SFUSER#MYWAREHOUSE@MYDB.MYSCHEMA>EXECUTE IMMEDIATE $SCRIPT;
001003 (42000): SQL compilation error:
syntax error line 4 at position 7 unexpected '('.
syntax error line 4 at position 15 unexpected '='.

SFUSER#MYWAREHOUSE@MYDB.MYSCHEMA>

However, when I try with the RETURN statement or a SELECT, I can successfully execute the script:

SFUSER#MYWAREHOUSE@MYDB.MYSCHEMA>SET SCRIPT =
                                  $$
                                  BEGIN
                                      LET count1 := 0;
                                      IF (count1 = 0) THEN
                                          RETURN 'echo asdfwerg > meow0111';
                                      END IF;

                                      RETURN 'MEOW';
                                  END;
                                  $$
                                  ;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.077s


SFUSER#MYWAREHOUSE@MYDB.MYSCHEMA>EXECUTE IMMEDIATE $SCRIPT;
+--------------------------+
| anonymous block          |
|--------------------------|
| echo asdfwerg > meow0111 |
+--------------------------+
1 Row(s) produced. Time Elapsed: 0.441s
SFUSER#MYWAREHOUSE@MYDB.MYSCHEMA>

Is there any way I can execute the !system statement from within the Snowflake script?

Thank you.

Solution

Those SnowSQL commands are specific to SnowSQL only, they are not part of the SQL, have to be run by themselves and you can't combine them with SQL query or SQL Scripting.

This is not supported.