MySQL Stored Procedure - how to reference internally created recordset

Question

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.

possible duplicate of How to use Table output from stored MYSQL Procedure — Michael - sqlbot
– Michael - sqlbot, Commented Aug 18, 2014 at 3:11

PinoyPal · Accepted Answer · 2014-08-18 03:10:58Z

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;

answered Aug 18, 2014 at 3:10

PinoyPal

3883 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Floobinator Over a year ago

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.

PinoyPal Over a year ago

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.

Collectives™ on Stack Overflow

MySQL Stored Procedure - how to reference internally created recordset

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related