Can I use Snowsight filters as a parameter to Snowflake Stored Procedure call?

user_10331351 0
Viewed 68

Is it possible to do anything with Snowsight filters other than literally

:= :MyFilter

I find this EXTREMELY limited...

For instance, many developers will want to use stored procedures and user defined functions rather than adhoc SQL... In which case, one would want to do this instead

call MyDb.MySchema.MyProc(:MyFilter);

I have found I can use this in a worksheet

SET MyVar = :Filter
call MyDb.MySchema.MyProc($MyVar);

and then I would have to turn this into a SQL Scripting block if using in a Dashboard... but I don't manage to get it working

DECLARE 
    MyVar STRING;
BEGIN
    SET MyVar = :MyFilter;
    
    CALL MyDb.MySchema.MyProc(:MyVar);
END;

I get an error Error: invalid identifier 'MyFilter'

Am I missing a trick?

My goal is to drive Dashboards from Stored procedures rather than adhoc code, hence I can use Source Control...

snowflake-cloud-data-platform
1 Answers

If you using Classic Console then use following code

EXECUTE IMMEDIATE $$
DECLARE 
    MyVar INTEGER;
    RetResultSet resultset;
BEGIN
     MyVar := 10;
    
     RetResultSet:= (CALL MyDb.MySchema.MyProc(:MyVar));
     return  table(RetResultSet);
END;
$$;

If you using SnowSight

 DECLARE 
        MyVar INTEGER;
        RetResultSet resultset;
    BEGIN
         MyVar := 10;
        
         RetResultSet:= (CALL MyDb.MySchema.MyProc(:MyVar));
         return  table(RetResultSet);
    END;
user_150889640
Related