Snowflake - Current bind variable substitution

Question

delete_cmd  := 'delete from test where rdate='||''':run_date'''||';';

The above code is not substituting the date '2022-09-07' assigned to "run_date.

Can you please help with the current substitution?

Answer 1

context helps, so lets write a Snowflake Scripting block that sounds like what you described:

declare
  delete_cmd text;
  run_date text;
begin
  run_date := '2022-09-07';
  delete_cmd  := 'delete from test where rdate='||''':run_date'''||';';
  return delete_cmd;
end;

and sure enough "it does not work good"

anonymous block
delete from test where rdate=':run_date';

So looking that code, we see you are concatenating three strings together of which the second is triple quoted, thus a string with an embedded quote mark AND the string tokens :run_date as text. so lets drop the triple quotes:

  delete_cmd  := 'delete from test where rdate='|| :run_date ||';';

now we get:

anonymous block
delete from test where rdate=2022-09-07;

so now the variable is substituted, but the quote is missing.. so we put the embedded quotes in the string parts (the before and after)

  delete_cmd  := 'delete from test where rdate='''|| :run_date ||''';';

the output becomes what we are after:

anonymous block
delete from test where rdate='2022-09-07';

Answer 2

I like to use $$ to keep things nice and clean

declare
  delete_cmd text;
  run_date text;
begin
  run_date := $$'2022-09-07'$$;
  delete_cmd  := $$delete from test where rdate=$$ || :run_date;
  return delete_cmd;
end;