Ad

Snowflake Stored Procedure Using Snowflake Scripting - Iterate Through Result And ALTER USER With Result

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 '[email protected]' 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 [email protected] 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?

Ad

Answer

Using EXECUTE IMMEDIATE:

create or replace procedure TEST_PROCEDURE()
  returns varchar
  language sql
  as
  $$
  declare
      c1 CURSOR for SELECT '"[email protected]"' 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 "[email protected]" SET DEFAULT_ROLE = 'REPORTER', DEFAULT_WAREHOUSE = 'REPORTING_WH_L'
Ad
source: stackoverflow.com
Ad