Dynamic SQL - Concatenate a variable and a string together for a varchar column

Question

I am struggling to get this code to work, not sure how to do it in PL/SQL like T-SQL, ive tried both:

-- input the Application name 
SET CustomRoleName = 'FakeApplicationName';

-- create the base custom roles
create role identifier($CustomRoleName) comment = 'This is ' || $CustomRoleName || ' read-only role';

but get error:

Syntax error: unexpected '||. (line 14)

and

-- input the Application name 
SET CustomRoleName = 'FakeApplicationName';

-- create the base custom roles
create role identifier($CustomRoleName) comment = 'This is ' + $CustomRoleName + ' read-only role';

but get error:

Syntax error: unexpected '+'. (line 14)

Cant find anything on the internet about concatenating a variable in Snowflake for a varchar column

user_5070879 · Answer

Using Snowflake Scripting:

DECLARE
   CustomRoleName    STRING DEFAULT 'FakeApplicationName';
   QUERY        STRING;
BEGIN
   QUERY:= REPLACE(
          'create role  comment = ''This is  read-only role'';'
          ,'', :CustomRoleName);    

   EXECUTE IMMEDIATE :QUERY;
   
   RETURN :QUERY;
END;

Output(classic UI):

Dynamic SQL - Concatenate a variable and a string together for a varchar column

1 Answers