snowchange appears to truncate my del statement

[TimNapierVST]

Hi,

Im trying to execute `USE DATABASE {{db_name}}; alter session set QUERY_TAG = 'DDL'; {%- set str_schema_name = 'DATA_MART' -%} {%- set str_table_name = 'DIM_CLIENT' -%} {%- set str_column_name = 'INDUSTRY' %}

USE DATABASE {{db_name}}; {%- set str_schema_name = 'DATA_MART' -%} {%- set str_table_name = 'DIM_CLIENT' -%} {%- set str_column_name = 'INDUSTRY' %}

begin if ( not exists( select * from information_schema.columns where table_schema = '{{str_schema_name}}' and table_name = '{{str_table_name}}' and column_name = '{{str_column_name}}' ) ) then begin alter table {{str_schema_name}}.{{ str_table_name }} ADD COLUMN {{str_column_name}} varchar(100); return '{{str_schema_name}}.{{str_table_name}}.{{str_column_name}} added'; end; else return '{{str_schema_name}}.{{str_table_name}}.{{str_column_name}} already exists'; end if; end;`

and when it is executed, it truncates to

`use database timn_db_blue; begin if ( not exists( select * from information_schema.columns where table_schema = 'DATA_MART' and table_name = 'DIM_CLIENT' and column_name = 'INDUSTRY' ) ) then begin alter table DATA_MART.DIM_CLIENT ADD COLUMN INDUSTRY varchar(100);

`

Is this supported, if not, is there a way I can check for the existence of the column prior to running the alter statement.

[CermitDFrog]

Are you using execute immediate for this?

[TimNapierVST]

Are you using execute immediate for this?

Yes

[CermitDFrog]

Looks to me like it's executing to the first return and then breaking out of the execute block. This is probably something to do with the python connectors execute_string method, but that's about as far as I can go.

This type of thing doesn't super track from a CICD/Deployment tool perspective however. Shouldn't you know the object state prior to attempting to edit?

[sfc-gh-tmathew]

Hello @TimNapierVST

Thank you for posting your question. A couple of observations to try and alter to see if it meets your needs

1. Avoid the use of the backtick.
2. The {%- set str_column_name = 'INDUSTRY' %} is missing corresponding closing identifier. Use {%- set str_column_name = 'INDUSTRY' -%} instead.
3. Finally, if you are building a anonymous stored procedure, try following the syntax for snowflake Scripting from the snowflake documentation.

Let us know how it goes with options suggested.