Snowflake stored procedure using Snowflake Scripting - Iterate through result and ALTER USER with result

user_11052801 0
Viewed 787

I am trying to create a stored procedure with Snowflake Scripting specifically to query a user table and then iterate through the results and alter those users.

Here is what I have so far:

create or replace procedure TEST_PROCEDURE()
  returns varchar
  language sql
  as
  $$
    declare
      c1 CURSOR for SELECT 'USERNAME@EMAIL.COM' AS NAME;
      user_name varchar;
    begin
      for record in c1 do
         user_name := record.name;
         ALTER USER user_name SET DEFAULT_ROLE = 'REPORTER', DEFAULT_WAREHOUSE = 'REPORTING_WH_L';
       end for;
      return user_name;
    end;
  $$
;

When I run the stored procedure I get the following error:

Uncaught exception of type 'STATEMENT_ERROR' on line 8 at position 9: SQL compilation error: User 'USER_NAME' does not exist or not authorized.

I changed USERNAME@EMAIL.COM just for this question it is querying a table and returning valid Snowflake user names.

The query history is showing that within the ALTER USER command it is not replacing user_name with the result from the cursor.

Any ideas on how to fix this?

snowflake-cloud-data-platform stored-procedures
3 Answers

Using EXECUTE IMMEDIATE:

create or replace procedure TEST_PROCEDURE()
  returns varchar
  language sql
  as
  $$
  declare
      c1 CURSOR for SELECT '"USERNAME@EMAIL.COM"' AS NAME;
      SQL STRING;
    begin
      for record in c1 do
         SQL := 'ALTER USER '|| record.name ||  ' SET DEFAULT_ROLE = ''REPORTER'', DEFAULT_WAREHOUSE = ''REPORTING_WH_L''';
        
         EXECUTE IMMEDIATE :SQL;
       end for;
      return :SQL;
    end;
$$;

CALL TEST_PROCEDURE();
-- ALTER USER "USERNAME@EMAIL.COM" SET DEFAULT_ROLE = 'REPORTER', DEFAULT_WAREHOUSE = 'REPORTING_WH_L'
user_50708790

looking at the using variables section of the help, in the example using a loop variable the variable names is in CAPS in the SQL

insert into names (v) values (:PV_NAME);

while lower case in the "script" part, also it's prefix by :

create procedure duplicate_name(pv_name varchar)
returns varchar
language sql
as
$$
    begin
        declare
            pv_name varchar;
        begin
            pv_name := 'middle block variable';
            declare
                pv_name varchar;
            begin
                pv_name := 'innermost block variable';
                insert into names (v) values (:PV_NAME);
            end;
            -- Because the innermost and middle blocks have separate variables
            -- named "pv_name", the INSERT below inserts the value
            -- 'middle block variable'.
            insert into names (v) values (:PV_NAME);
        end;
        -- This inserts the value of the input parameter.
        insert into names (v) values (:PV_NAME);
        return 'Completed.';
    end;
$$
;
user_439920

You need to use IDENTIFIER and add colon in front of the user_name variable.

So change from

ALTER USER user_name 
  SET DEFAULT_ROLE = 'REPORTER', 
      DEFAULT_WAREHOUSE = 'REPORTING_WH_L';

to

ALTER USER IDENTIFIER(:user_name) 
  SET DEFAULT_ROLE = 'REPORTER', 
      DEFAULT_WAREHOUSE = 'REPORTING_WH_L';

I have tested and it works for me.

user_98793790
Related