Returning a Result Set from a Stored Procedure

You can use the RETURNS option to return a result set, in the form of a temporary table, from a stored procedure. This temporary table can only be inserted into. It cannot be referenced, from inside the stored procedure, using a SQL SELECT, UPDATE or DELETE statement.

Examples

Example 1: Using the RETURNS option to return a result set of data from a database table

The INSERT statement must be a valid insert statement as supported by NuoDB. The values provided can be either a select statement, a values list, or a combination of both (see INSERT for examples of valid syntax).

/* Using select statement as values for INSERT statement */
DROP PROCEDURE IF EXISTS prc_player_position;
SET DELIMITER @
CREATE PROCEDURE prc_player_position (IN in_pos STRING)
      RETURNS tmp_tab ( number INTEGER,
                        name   STRING,
                        team   STRING)
AS
      INSERT INTO tmp_tab SELECT number, name, team
                           FROM hockey.hockey
                           WHERE position = in_pos
                            ORDER BY number;
END_PROCEDURE
@
SET DELIMITER ;
EXECUTE prc_player_position('Defense');
 NUMBER        NAME         TEAM
 ------- ----------------- ------
   21    ANDREW FERENCE    Bruins
   27    DOUGIE HAMILTON   Bruins
   33    ZDENO CHARA       Bruins
   44    DENNIS SEIDENBERG Bruins
   45    AARON JOHNSON     Bruins
   54    ADAM MCQUAID      Bruins
   55    JOHNNY BOYCHUK    Bruins

/* Using a combination of select statement and a values column list to provide
/* values for INSERT.
/* In this case, using a table alias in the select statement is required */

DROP PROCEDURE IF EXISTS prc_player_position;
SET DELIMITER @
CREATE PROCEDURE prc_player_position (IN in_pos STRING)
      RETURNS tmp_tab ( id       INTEGER,
                        number   INTEGER,
                        name     STRING,
                        position STRING,
                        team     STRING,
                        date     DATE,
                        active   BOOLEAN)
AS
      INSERT INTO tmp_tab SELECT h.*, current_date, 'TRUE'
                           FROM hockey.hockey h
                           WHERE h.position = in_pos
                           ORDER BY h.id;
END_PROCEDURE
@
SET DELIMITER ;
EXECUTE prc_player_position('Defense');
 ID  NUMBER        NAME        POSITION   TEAM     DATE    ACTIVE
 --- ------- ----------------- --------- ------ ---------- -------
 15    55    JOHNNY BOYCHUK     Defense  Bruins 2015-04-23  TRUE
 16    33    ZDENO CHARA        Defense  Bruins 2015-04-23  TRUE
 17    21    ANDREW FERENCE     Defense  Bruins 2015-04-23  TRUE
 18    27    DOUGIE HAMILTON    Defense  Bruins 2015-04-23  TRUE
 19    45    AARON JOHNSON      Defense  Bruins 2015-04-23  TRUE
 20    54    ADAM MCQUAID       Defense  Bruins 2015-04-23  TRUE
 21    44    DENNIS SEIDENBERG  Defense  Bruins 2015-04-23  TRUE

Example 2: Accessing the result set defined by the RETURNS option

The result set defined by the RETURNS option is an in-memory temporary table that does not support SELECT, UPDATE or DELETE SQL statements. Attempting to access it with any SQL statement, other than INSERT, will throw an exception. The following examples show attempts to select from and update the result set and the exception that is returned. The last case shows multiple attempts to insert into the table, which is valid.

USE test;
SET DELIMITER @
    Delimiter is now [@]

CREATE PROCEDURE prc_test_rs
   RETURNS temp_table(col1 int, col2 string, col3 string)
AS
     VAR l_cnt INT = 1;
     VAR l_name STRING;
     INSERT INTO temp_table (col2)
       SELECT name FROM hockey.hockey;
     FOR SELECT col2 from temp_table FOR UPDATE;
         l_name=col2;
         UPDATE temp_table SET col1 = l_cnt+1;
         l_cnt=l_cnt+1;
     END_FOR;
END_PROCEDURE;
@
Table TEST.TEMP_TABLE does not support select | update | delete access

CREATE PROCEDURE prc_test_rs
   RETURNS temp_table(col1 int, col2 string, col3 string)
AS
     INSERT INTO temp_table (col1,col2)
        SELECT number,name FROM hockey.hockey;
     UPDATE temp_table SET col3 = 'source: prc_test_rs';
END_PROCEDURE;
@
Table TEST.TEMP_TABLE does not support update access

CREATE PROCEDURE prc_test_rs
   RETURNS temp_table(col1 int, col2 string, col3 string)
AS
     INSERT INTO temp_table (col1,col2,col3)
        SELECT number,name,'first insert' FROM hockey.hockey WHERE number < 20;
     INSERT INTO temp_table (col1,col2,col3)
        SELECT number,name,'second insert' FROM hockey.hockey WHERE number > 60;
END_PROCEDURE;
@
SET DELIMITER ;
    Delimiter is now [;]

CALL prc_test_rs;

 COL1        COL2           COL3
 ----- ---------------- -------------
  11   GREGORY CAMPBELL first insert
  18   NATHAN HORTON    first insert
  17   MILAN LUCIC      first insert
  19   TYLER SEGUIN     first insert
   1   MAX SUMMIT       first insert
  64   LANE MACDERMID   second insert
  63   BRAD MARCHAND    second insert
  91   MARC SAVARD      second insert