Is there an equivalent of try-catch blocks in pure SQL in Snowflake for handling errors?
I am looking to create a procedure that will check all views and perform some action on them, skipping the invalid ones. The following JavaScript version accomplishes 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
When attempting to include an exception within the loop, a syntax error is encountered. Placing it elsewhere results in 'break is outside of loop' error. Are there any hidden typos in the code that are causing this issue?
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;