0

I am curious how to reference an existing stored procedure SELECT statement from a secondary query or SET call within the same stored procedure.

For example:

CREATE PROCEDURE 'mysp' (OUT sumvalue INT)
BEGIN
    -- This is the core recordset the SP returns
    SELECT * FROM Table

    -- I also want to return a value based on the above recordset
    SET sumvale = SUM(previousselect.values)
END

Essentially, I have a SP that returns a detailed recordset, and I need to return SUM and custom values based on the data within that recordset. The issue is I cannot figure out how to reference the data after the SELECT statement (e.g. does it create an internal reference I can use such as @recordset1.X).

Any help would be appreciated.

Floobinator
  • 388
  • 2
  • 11
  • possible duplicate of [How to use Table output from stored MYSQL Procedure](http://stackoverflow.com/questions/17944871/how-to-use-table-output-from-stored-mysql-procedure) – Michael - sqlbot Aug 18 '14 at 03:11

1 Answers1

1

Try using cursor from this link:

As MySql does not allow you to return a recordset from either store procedures or functions, you could try this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `some_procedure`(out some_id int)
BEGIN
    declare done boolean default false;
    declare id int;
    declare tot decimal(10,2);

    declare some_cursor cursor for 
       select id, total from some_table where id = some_id;

    declare continue handler for not found set done = true;

    open some_cursor;
    loop1: loop
        fetch some_cursor into id, tot;
        if done=true then
            leave loop1;
        end if;

        //do your calculation here or whatever necessary you want to do with the code

    end loop loop1;
END;
PinoyPal
  • 388
  • 3
  • 12
  • Hmm; this seems overly complex. Note I could create a SP specifically to get the Sum of the values, but I don't want to create redundant calls (seems inefficient). There's no way to reference the recordset of a SELECT statement within the stored procedure after it's been executed? Note we're using Node.js as the web processing framework and the stored procedures return recordsets just fine to it, so not sure what you mean by "MySql does not allow you to return a recordset from ... store[d] procedures". They return recordsets just fine. – Floobinator Aug 18 '14 at 03:32
  • Yes, procedures can return recordsets from any various sql queries but does not by calling from the inside of another stored procedures. I've been in that situation before and I ended up using cursors instead with mysql stored views. – PinoyPal Aug 18 '14 at 07:13