How do I run a SHOW WAREHOUSES command within a stored procedure?

user_18290200 0
Viewed 39

I'm trying to work on a stored procedure that users can run to change their default warehouse and it only has one input variable: the warehouse the user wants to change their default too. I want to add validation where it checks that the user's inputted warehouse variable exists.

So I want to be able to run a 'SHOW WAREHOUSES LIKE :warehouse' query in a stored procedure, and do a SELECT COUNT (*) on this to count how many warehouses there are like the input variable :warehouse so I can validate that it exists or not. I can't figure out how to do this so does anyone know a way that I can do this, with a code example so I can see the syntax?

In steps, what I want to do is : (1) Run a SHOW WAREHOUSES LIKE query, (2) Get a count of how many warehouses there are like the input variable :warehouse, (3) Then do an IF statement like "IF warehouse_check =0 THEN RETURN 'warehouse doesn't exist'".

What I've tried so far (not a SQL expert by any means, still learning so syntax may be very bad)

    sql_command := 'SHOW WAREHOUSES LIKE '||:warehouse||';';
    EXECUTE IMMEDIATE :sql_command;
    
    warehouse_check := 'SELECT COUNT (*) FROM (RESULT_SCAN ( { LAST_QUERY_ID() } ))';
    EXECUTE IMMEDIATE :warehouse_check;
    
    IF (warehouse_check =0) THEN
        RETURN 'No warehouse with that name';
    END IF;

and also

    usage_check := 'SELECT (SHOW WAREHOUSES LIKE '||:warehouse||');';
    
    warehouse_check := (select count(*) from table(result_scan(last_query_id(-1))) where "name" = :warehouse);
    
    IF (warehouse_check = 0) THEN
        RETURN 'No warehouse with that name';
    END IF;

If anyone has any ideas, it'd be much appreciated!

snowflake-cloud-data-platform sql
1 Answers

This one sets the default warehouse for the user. SP should be created by a powerful role something like accountadmin:

CREATE OR REPLACE PROCEDURE DEFAULT_WAREHOUSE( wname VARCHAR )
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS OWNER
AS
DECLARE
   sql_command VARCHAR;
   set_warehouse VARCHAR;
   warehouse_check VARCHAR;
   res RESULTSET;
BEGIN
    sql_command := 'SHOW WAREHOUSES LIKE '''||:wname||''';';
    res := (EXECUTE IMMEDIATE :sql_command);
    let c1 cursor for res;    
    open c1;
    fetch c1 into warehouse_check;
    close c1;
    IF ( warehouse_check = UPPER(wname) ) THEN
        set_warehouse := 'ALTER USER ' || current_user() || ' SET DEFAULT_WAREHOUSE=' || warehouse_check;
        EXECUTE IMMEDIATE set_warehouse;
        RETURN 'Warehouse is set as default for user ' || current_user();
    END IF;
    RETURN 'No warehouse with that name';
END;
user_125509650
Related