Filter
Top Products
CHAPTER 6 Using Procedures and Batches
259
CREATE PROCEDURE OuterProc()
ON EXCEPTION RESUME
BEGIN
DECLARE res CHAR(5);
MESSAGE ’Hello from OuterProc.’;
CALL InnerProc();
SELECT @res=SQLSTATE;
IF @res=’52003’ THEN
MESSAGE ’SQLSTATE set to ’,
res, ’ in OuterProc.’;
END IF
END;
CREATE PROCEDURE InnerProc()
ON EXCEPTION RESUME
BEGIN
DECLARE column_not_found
EXCEPTION FOR SQLSTATE ’52003’;
MESSAGE ’Hello from InnerProc.’;
SIGNAL column_not_found;
MESSAGE ’SQLSTATE set to ’,
SQLSTATE, ’ in InnerProc.’;
END
The following statement executes the OuterProc procedure:
CALL OuterProc();
The message window of the server then displays the following:
Hello from OuterProc.
Hello from InnerProc.
SQLSTATE set to 52003 in OuterProc.
The execution path is as follows:
1 OuterProc executes and calls InnerProc.
2 In InnerProc, the SIGNAL statement signals an error.
3 The MESSAGE statement is not an error-handling statement, so control is
passed back to OuterProc and the message is not displayed.
4 In OuterProc, the statement following the error assigns the SQLSTATE
value to the variable named
res. This is an error-handling statement, and
so execution continues and the OuterProc message is displayed.