How to run multiple SQL statements

[yohei1126]

Hi,

When we run multiple SQL statements in task or stored procedure using snowsql, we use !set sql_delimiter=/ for snowsql can parse delimiter properly.

https://docs.snowflake.com/en/sql-reference/sql/create-task.html#multiple-sql-statements-using-snowsql

!set sql_delimiter=/
CREATE OR REPLACE TASK test_logging
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
  SCHEDULE = 'USING CRON  0 * * * * America/Los_Angeles'
AS
BEGIN
  ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
  SELECT CURRENT_TIMESTAMP;
END;/
!set sql_delimiter=";"

but this does not work for schemachange. We got this error. How can we run this?

snowflake.connector.errors.ProgrammingError: 001003 (42000): SQL compilation error:
syntax error line 1 at position 0 unexpected '!'.

[UberDoodles]

Commands like !set are specific to snowsql CLI. schemachange does not use that CLI, so they won't work.

To create stored procedures with multiple statements, we use $$ around the procedure body, and this has always worked well for us :

CREATE OR REPLACE TASK test_logging
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
  SCHEDULE = 'USING CRON  0 * * * * America/Los_Angeles'
AS
$$
  ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
  SELECT CURRENT_TIMESTAMP;
$$;

You can also use single quotes around the procedure body, but this gets very messy because you have to escape any single-quotes in the procedure body :

CREATE OR REPLACE TASK test_logging
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
  SCHEDULE = 'USING CRON  0 * * * * America/Los_Angeles'
AS
'
  ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF'';
  SELECT CURRENT_TIMESTAMP;
';

[yohei1126]

thanks I will try it