Catch stored procedure output into a variable without using out params

Question

Consider the following procedure:

CREATE PROCEDURE `getFoo`()
BEGIN
    select 'bar' as foo;
END;

Once call-ed, it outputs:

{"foo":"bar"}

But what if getFoo is invoked within a different stored procedure, how do I catch its result content into a variable, like this?

CREATE PROCEDURE `masterProc`()
BEGIN
    call `getFoo`() into @foo;
    select @foo as foo;
END;

This outputs the following error when invoked: sqlMessage: "FUNCTION db.getFoo does not exist"

I am aware of the available options involving out parameters, but those are not viable resolution(s) to my problem.

Obligation(s)

getFoo cannot be altered. It will output the results of a SELECT statement without involving any variables nor parameters.

What I've tried

Unfortunately, all will output errors.

set @foo = exec getFoo(); set @foo = call getFoo(); select getFoo() into @foo; call getFoo() into @foo;

I haven't come across currentFunction: in mysql before - do you have a reference for it? — P.Salmon, Nov 02 '20 at 11:01
@P.Salmon Snap I forgot to remove it. It is used for `GOTO` and `leave` purposes. Edited the question and removed *statement labels*. — Constantin, Nov 02 '20 at 11:02
https://dev.mysql.com/doc/refman/8.0/en/statement-labels.html — Constantin, Nov 02 '20 at 11:03
Yeah there is no `GOTO` statement by itself, but there are workarounds to achieve similar-to-goto workflows. — Constantin, Nov 02 '20 at 11:04
@P.Salmon That throws `ER_PARSE_ERROR`. See **What I've tried** — Constantin, Nov 02 '20 at 11:10
SP's output is sent to output stream and cannot be re-captured from there by SQL code. Either OUT parameters or external storage (user-defined variable, temporary table, etc.) may help. — Akina, Nov 02 '20 at 11:16
You should maybe tell us what you are trying to achieve, rather than trying to 'fix' a non workable solution. — P.Salmon, Nov 02 '20 at 11:21
Yes, noway. SP output cannot be used as row source for a query in MySQL. — Akina, Nov 02 '20 at 11:22
@P.Salmon thanks for the effort but I think what I want to achieve is pretty clear within my question. Evidently, there is no working/workable solution attached but just trials, and including them is how a proper SO question should be structured. — Constantin, Nov 02 '20 at 11:23

slaakso · Answer 1 · 2020-11-02T11:53:03.940

0

You cannot catch the result set (from SELECT) from another procedure. You have couple of options to do similar thing:

Put the result set in temporary table. Works for multiple rows.
Use OUT-variable (best for single values)
Use user-defined variable (@var) (be careful with these as calls to other procedure can change the values)

Create the getFoo():

CREATE PROCEDURE getFoo()
BEGIN
drop temporary table if exists temptable;

create temporary table temptable 
as 
select col1, col2 FROM table1;

END;

Create the masterProc():

CREATE PROCEDURE masterProc()
BEGIN
    call getFoo();

    select *
    from temptable;
END $$

Call masterProc():

call masterProc();

edited Nov 02 '20 at 11:53

answered Nov 02 '20 at 11:38

slaakso

8,331
2
16
27

Can you attach a working example whereas `getFoo` outputs a table-like result and not a single column-value? Highly appreciated, thank you – Constantin Nov 02 '20 at 11:45
@Constantin Example with temporary table added – slaakso Nov 02 '20 at 11:53

score -1 · Answer 2 · answered Nov 02 '20 at 11:11

-1

The only way you can invoke an SP in mysql is with a CALL the error you get is because you are attempting to invoke a function which does not exist. User defined variables (at variables) are available anywhere

drop procedure if exists getfoo;
drop procedure if exists masterproc;
delimiter $$

CREATE PROCEDURE `getFoo`()
BEGIN
    set @foo = 'foo';
END $$

CREATE PROCEDURE `masterProc`()
BEGIN
    call `getFoo`();
    select @foo as foo;
END $$

delimiter ;

call masterproc();

+------+
| foo  |
+------+
| foo  |
+------+
1 row in set (0.001 sec)

answered Nov 02 '20 at 11:11

P.Salmon

17,104
2
12
19

Your solution does not catch a *stored procedure*'s `SELECT` output into a variable of a master procedure. Your master procedure selects a variable persisting in the nested procedure, instead. – Constantin Nov 02 '20 at 11:13
@Constantin *catch a stored procedure's SELECT output into a variable of a master procedure* Impossible. – Akina Nov 02 '20 at 11:17

Catch stored procedure output into a variable without using out params

2 Answers2