Try&catch clause in snowflake scripting-CodePudding

I wonder if is there equivalent of try catch blocks from javascript in pure sql in Snowflake. I want to have a procedure which will check all views and does something with them, but some of the views are invalid and these I want to skip. The javascript version looks like this:

create or replace procedure test_views_js()
returns varchar
language javascript
as 

$$
var sql = "select table_name from INFORMATION_SCHEMA.views where table_schema='TEST'";
var stmt = snowflake.createStatement ({sqlText:sql});
var result_set = stmt.execute();
var cnt = 0;
while (result_set.next()){
try{
    var sql_text = "select count(*) from " result_set.getColumnValue(1);
    var stmt2 =  snowflake.createStatement ({sqlText:sql_text});
    var r = stmt2.execute();
    r.next();
    cnt =r.getColumnValue(1);
}catch (err){
continue
}

}
return cnt;
$$

Can I achieve the same result with sql?

UPDATE

I am getting syntax error when I try to put exception in the loop. When it's in different place syntax is valid, but 'break is outside of loop' . Maybe there is some obvious typo that i cannot see?

create or replace procedure test_views() 
returns integer not null 
language sql 
as 
declare 
sel varchar; 
row_cnt integer default 0; 
res resultset default 
    (select table_name 
     from INFORMATION_SCHEMA.views 
     where table_schema='TEST') ; 
c1 cursor for res; 

begin 
    for row_variable in c1 do 
        row_cnt:= (select count(*) from view_test); 
        exception when statement_error then continue; 
    end for; 
    return row_cnt; 
end;

CodePudding user response：

It is possible to use exception inside loop. Instead of:

for row_variable in c1 do 
        row_cnt:= (select count(*) from view_test); 
        exception when statement_error then continue; 
end for;

All the code should be wrapped with its own BEGIN EXCEPTION END block.

create or replace procedure test_views() 
returns integer not null 
language sql 
as
declare 
sel varchar; 
row_cnt integer default 0; 
res resultset default 
    (select table_name 
     from INFORMATION_SCHEMA.views 
     where table_schema='TEST') ; 
c1 cursor for res; 

begin 
    for row_variable in c1 do 
        begin
            row_cnt:= (select count(*) from view_test); 
        exception when other then continue; 
        end;
    end for; 
    return row_cnt; 
end;

CodePudding user response：

Yes - there RAISE/EXCEPTION constructs documented here: https://docs.snowflake.com/en/developer-guide/snowflake-scripting/exceptions.html