Controlling the Return from a Stored Procedure

Returning From a Stored Procedure

There are two ways to control the return from a stored procedure.

Return

Use the RETURN statement to successfully terminate the execution of a stored procedure early.

RETURN;

Return Example:

SET DELIMITER @
CREATE PROCEDURE prc_champ_years(IN iPlayerid string, OUT oYear integer, OUT oTeam string)
AS
   /* get the first year the player played on a team ranked #1 */
    FOR SELECT t.YEAR,t.TEAMID
          FROM SCORING s
            JOIN TEAMS t on t.year = s.year and t.teamid = s.teamid
         WHERE s.playerid = iPlayerid and t.rank = 1
           ORDER by t.YEAR;
        oYear = YEAR;
        oTeam = TEAMID;
        RETURN;
    END_FOR;
    oYear = 0;
    oteam='No Championships';
END_PROCEDURE
@

CREATE PROCEDURE PRC_PLAYER_CHAMPS(IN iPlayerid string)
AS
    VAR oYear integer, oTeam string, output string;
    var playername=(select LastName||','||Firstname from players where playerid = iPlayerid);
    if (playername is null)
        output= 'no player exists with that ID';
    else
        call prc_champ_years(iPlayerid, oYear, oTeam);
        output='Played for '||oTeam||' in '||oYear;
    end_if;
    THrow output;
END_PROCEDURE
@
SET DELIMITER ;

CALL prc_player_champs('robergo02');
Procedure HOCKEY.PRC_PLAYER_CHAMPS, Played for SEA in 1917

CALL prc_player_champs('byramsh01');
Procedure HOCKEY.PRC_PLAYER_CHAMPS, Played for No Championships in 0

CALL prc_player_champs('xxx');
Procedure HOCKEY.PRC_PLAYER_CHAMPS, no player exists with that ID

Throw

Use the THROW statement to terminate the execution of a stored procedure and report messages.

THROW string;

Throw Example:

DROP PROCEDURE IF EXISTS prc_get_player;
SET DELIMITER @
CREATE PROCEDURE prc_get_player (IN iNum integer)
 AS
    var outdispl string;
    outdispl = (SELECT NAME||' Plays '||POSITION||' For '||TEAM FROM HOCKEY WHERE NUMBER= iNum);
    THROW outdispl;
END_PROCEDURE;
@
SET DELIMITER ;

call prc_get_player(37);
Procedure HOCKEY.PRC_GET_PLAYER, PATRICE BERGERON Plays Forward For Bruins