How to call different stored procedures from snowflake based on the value in a selected row in app

Discussion
,
1

I have a table in my app that returns/displays one record during each run. I would like to essentially case on a column value on the selected record, and depending on what the value is, call a stored procedure from snowflake.

something like

CASE 
         WHEN {{table.selectedRow.column1.value}} = 'A'
         THEN 
                   CALL SCHEMA.PROCEDURE_1( {{table.selectedRow.column1.value}} )
         WHEN {{table.selectedRow.column1.value}} = 'B'
         THEN
                   CALL SCHEMA.PROCEDURE_2( {{table.selectedRow.column1.value}}

and so on. Is there a way to set something like this up? Since I wouldn't be able to just run a CASE sql statement on its own.

More specifically this is my table, when I click the update eligibility button in the table for the record, I would like it to run something where it checks value of ISO Country field, then checks whether that value is equal to a specific country and run the stored procedure for that country. Not sure whether this can be done in sql mode or something else?

Screenshot 2024-05-31 at 10.50.13
Screenshot 2024-05-31 at 10.50.131922×566 55.9 KB

2

hey @abalamur did the code you provided not work? if not, maybe if you wrap in single quotes around the double brackets {{ }}? haven't used snowflake specifically, but have done some dynamic queries with interpolated Retool values and sometimes it can be tricky at first

1 Like
3

I think you could setup two SQL queries and trigger the appropriate one using a JS query if / else if / else structure. Something like:

let foo = table.selectedRow.column1.value;

if (foo == 'A') {
    querySchemaProc1.trigger();
} else if (foo == 'B') {
    querySchemaProc2.trigger();
} else {
    console.log('These are not the droids you are looking for.');
}
2 Likes