CODEWITHSUNDEEP
snowflake-cloud-data-platform
DuckDBAT55
DuckDBAT55

Reputation: 35

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

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

Upvotes: 2

Views: 2039

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

Using Snowflake Scripting:

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

   EXECUTE IMMEDIATE :QUERY;
   
   RETURN :QUERY;
END;

Output(classic UI):

enter image description here

Upvotes: 1

Related Questions