I am trying to add the continue handler on stored procedure which will find the invalid views, like if underlying table/column deleted, then help view return 3807 sql code and sql state 42000,at this point i need to further process the cursor and insert the failed view name onto some table. I tried below with no success, Any ideas/inputs to below stored procedure?
create procedure findinvalidview() begin declare sqltxt varchar(100); for cur as select databasename as dbname,tablename as tbname from dbc.tables where tablekind='V' and databasename='TEMP'; set sqltxt='help view '||cur.dbname||'.'||cur.tbname||';'; execute immediate sqltxt; if sqlcode = 3807 OR sqlstate = 42000 then insert into invalidviewlist(cur.dbname,cur.tbname,:sqlstate); else donothing; end if; end for; end
GANGA SANDEEP KUMAR
You can't use HELP in a stored procedure (and even if you could, note that as a data-returning statement it would require a cursor and would not be valid for EXECUTE IMMEDIATE).
Maybe try issuing a PREPARE of a "SELECT * FROM viewname;" statement instead.