Using a Result Set as Input to a Stored Procedure

NuoDB SQL supports a result set being defined as an input parameter to a stored procedure. This allows the stored procedure to process this result set like a table, but it can only be read once. Any attempt to read the result set a second time will return an error. This result set is read only and cannot be manipulated by DML SQL statements inside the stored procedure. When passing a SELECT statement as the result set, the SELECT statement must be enclosed in parenthesis. All input parameters need to be enclosed in parenthesis. Therefore, when you have a SELECT statement as an input parameter, it needs to be enclosed in double parenthesis.

See CREATE PROCEDURE for more information about the arguments to the CREATE PROCEDURE command.

Example

DROP PROCEDURE prc_rs_input;
SET DELIMITER @
CREATE PROCEDURE prc_rs_input (IN tmphockey (number INTEGER, name STRING) )
RETURNS temptab ( number INTEGER, name STRING, position STRING)
AS
    INSERT INTO temptab
      SELECT h.number+1000, h.name, h.position
        FROM tmphockey t, hockey h WHERE h.number = t.number;
END_PROCEDURE;
@
SET DELIMITER ;
CALL prc_rs_input((SELECT number, name FROM hockey));

 NUMBER        NAME        POSITION
 ------- ----------------- ---------

  1037   PATRICE BERGERON   Forward
  1048   CHRIS BOURQUE      Forward
  1011   GREGORY CAMPBELL   Forward
. . .