Snowflake: how to throw an exception in a stored procedure if it is called inside the main procedure doesn't exist or not callable?

Question

I have a procedure P1 which is calling procedure P2. I need to handle the exception when P2 doesn't exist.

Currently it is throwing

Uncaught exception of type 'STATEMENT_ERROR' on line 3 at position 4 : SQL compilation error: Unknown user-defined function P2'

Is there any way to handle this exception differently?

P1()
BEGIN
    call P2();
END;

Gokhan Atil · Answer

Can you try something like this to handle exceptions?

create or replace procedure test_procedure()
    returns VARCHAR
    language sql
AS
declare
      msg VARCHAR DEFAULT '';
begin
  begin
    CALL P2();
  exception
    when other then
     msg := msg || ' P2 does not exist but it is OK...';
  end;
 begin 
    CALL P3();
  exception
    when other then
     msg := msg || ' P3 does not exist but it is OK';
  end;
 return msg;
end;

CALL test_procedure();

Snowflake: how to throw an exception in a stored procedure if it is called inside the main procedure doesn't exist or not callable?

1 Answers