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.

One or more of the examples below make use of tables defined in the HOCKEY schema, which is a schema defined in the test database. The test database is created when the QuickStart process is run. See Running the SQL QuickStart.

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.

Note: 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     
...