Snowflake Stored Procedure Using Snowflake Scripting - Iterate Through Result And ALTER USER With Result
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.
[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?
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'
- → How to execute Stored Procedure from Laravel
- → Laravel 5 - Cannot find MySQL Stored Procedure
- → Performance and Security with Laravel Query Builder vs Stored Procedures
- → How to insert numbers from 1 to 1000 to a table what i created in a procedure?
- → MySQL select query with where condition using string variables
- → how to pass dynamic table name into mySQL Procedure with this query?
- → How to migrate schemas in postgresql from local server to production server?
- → Springboot java oracle procedure calling with cursor
- → Spring SimpleJdbcCall and Oracle Synonyms
- → Insert into statement not working inside a function (PostgreSQL)
- → How to use XML to configure a Store procedure
- → SQL Server Fast Forward Cursors
- → Do I have to use Stored Procedures to get query level security or can I still do this with Dynamic Sql?