I have a stored procedure which calls another stored procedure using dynamic SQL. A simplified version of the code is provided here.
CREATE DEFINER=`cmdb`@`%` PROCEDURE `testCall`(procName VARCHAR(100))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS @num = NUMBER;
GET DIAGNOSTICS CONDITION @num @message = MESSAGE_TEXT, @errNo = RETURNED_SQLSTATE ;
if @errorMessage is null then
Set @errorMessage = "No Error Message";
end if;
select @errorMessage;
END;
select concat("call ", procName, "()") into @proc;
Prepare stmt FROM @proc;
Execute stmt;
deallocate prepare stmt;
END
If intent is to call an arbitrary stored procedure using testCall. When I call testCall with a non-existent SP name as input as following,
call testCall("storedProc4");
I see the following error message:
"No Error Message"
I expect to see something like below:
"PROCEDURE storedproc4 does not exist"
How to catch this exception? I have tried a CONTINUE handler for error 1305 and an EXIT handler as well with the similar results.