Returning Multiple Result Sets from a Stored Procedure

NuoDB SQL supports returning multiple result sets from a single stored procedure. This allows for the ability to retrieve related but completely different data structures while avoiding having to make multiple calls to more than one stored procedure.

We can create a stored procedure prc_player_info(), pass in a parameter, the year, and return information related to teams that won their division. At the same time, we can return the player roster for the team that won the final playoffs.

As illustrated in the following example, when creating the returned table directly with a SQL SELECT statement, which is used to create the 1st returned table, the ORDER BY clause is eliminated by the NuoDB optimizer, for performance reasons. If the result set must be returned in a specified order, use the INSERT INTO…SELECT…ORDER BY syntax, which is used to create the 2nd returned table.

DROP PROCEDURE prc_player_info;
SET DELIMITER @
CREATE PROCEDURE prc_player_info (IN i_year INTEGER)
  RETURNS PlayerTeams (TeamYear INT,TeamName STRING,TeamDiv STRING,TeamPlayoff STRING)
         , TABLE PlayersRoster (PlayerFirstName STRING, PlayerLastNAme STRING, PlayerPosition STRING )
AS
  PlayerTeams = (SELECT year, Name, divisionid, playoff FROM teams
             WHERE year = i_year AND rank = 1
             ORDER BY Name);
    INSERT INTO PlayersRoster (SELECT p.firstname,p.lastname, p.position
            FROM players p ,scoring s, teams t
             WHERE p.playerid = s.playerid
             AND s.year = t.year
               AND s.teamid = t.teamid
              AND t.year = i_year
              AND t.rank = 1
              AND t.playoff = 'F'
         ORDER BY p.lastname, p.firstname);
    RETURN;
END_PROCEDURE;
@
SET DELIMITER ;
CALL  prc_player_info(1976);
 TEAMYEAR       TEAMNAME       TEAMDIV  TEAMPLAYOFF
 --------- ------------------- -------- ------------

   1976    Boston Bruins          AD        F
   1976    Montreal Canadiens     NO        SC
   1976    Philadelphia Flyers    PT        SF
   1976    St. Louis Blues        SM        QF
   1976    Houston Aeros          WW        AVSF
   1976    Quebec Nordiques       EW        AVC

 PLAYERFIRSTNAME  PLAYERLASTNAME  PLAYERPOSITION
 ---------------- --------------- ---------------

     Earl            Anderson            R
     John            Bucyk               L
     Wayne           Cashman             L
...

Multiple result sets can also be populated by nesting stored procedures. In the example below, the result sets returned by the stored procedure can be populated by calling the stored procedure in the previous example.

DROP PROCEDURE IF EXISTS prc_call_player_info ;
SET DELIMITER @
CREATE PROCEDURE  prc_call_player_info (IN i_year INTEGER)
    RETURNS TABLE PlayerTeams (TeamYear INT,TeamName STRING,TeamDiv STRING,TeamPlayoff STRING)
         , TABLE PlayersRoster (PlayerFirstName STRING, PlayerLastNAme STRING, PlayerPosition STRING )
AS
    PlayerTeams,PlayersRoster = CALL prc_player_info(i_year);
    RETURN;
END_PROCEDURE;
@
SET DELIMITER ;
CALL prc_call_player_info(1976);


TEAMYEAR       TEAMNAME       TEAMDIV  TEAMPLAYOFF
 --------- ------------------- -------- ------------

   1976    Boston Bruins          AD        F
   1976    Houston Aeros          WW        AVSF
   1976    Montreal Canadiens     NO        SC
   1976    Philadelphia Flyers    PT        SF
   1976    Quebec Nordiques       EW        AVC
   1976    St. Louis Blues        SM        QF

 PLAYERFIRSTNAME  PLAYERLASTNAME  PLAYERPOSITION
 ---------------- --------------- ---------------

     Earl            Anderson            R
     John            Bucyk               L
     Wayne           Cashman             L
...