5

I have a non-trivial SELECT statement and I don't want to write it twice (standard SW development considerations). I want to use the results in two stored procedures. I am imagining something like this:

CREATE PROCEDURE consumerProcedureA()
BEGIN
    CALL supplierProcedure();
    -- ** insert magic here **
END;

CREATE PROCEDURE consumerProcedureB()
BEGIN
    CALL supplierProcedure();
    -- ** insert magic here **
END;

CREATE PROCEDURE supplierProcedure()
BEGIN
    SELECT field1, field2, field3, MESSYEXPR AS field4
    FROM (complicated sub-SQL query) -- blah blah blah
    ;
END;

Grant you, I could create a view to capture the common query, but is there a way to have a calling stored procedure access and process the result set returned by a called stored procedure?

  • 3
    The only thing that comes to mind is using temp tables to "transfer" the data from one procedure to the other. – a_horse_with_no_name Mar 22 '14 at 17:40
  • @a_horse_with_no_name is right; tables are the only way to pass non-primitive results, unless you're into ugly string concatenation & parsing (as in aggregating the entire result set into one long string). – Shlomi Noach Mar 24 '14 at 5:53
2

I am looking for a way to do this myself (in a way that satisfies me..), but a perfectly valid way to do so is described in this Stack Overflow Q & A:

Calling a Stored Procedure in a Stored Procedure in MySQL

The accepted answer by AhamedMustafaM demonstrates a technique using OUT parameters with the following code:

    CREATE PROCEDURE innerproc(OUT param1 INT)
    BEGIN
     insert into sometable;
     SELECT LAST_INSERT_ID() into param1 ;
    END
    -----------------------------------
    CREATE PROCEDURE outerproc()
    BEGIN
    CALL innerproc(@a);
    // @a gives you the result of innerproc
    SELECT @a INTO variableinouterproc FROM dual;
    END

I'm sure not always the answer for what you are trying to accomplish, but it is certainly an option. While you couldn't get the direct result of what the procedure called, this would help you get individual values, possibly using multiple OUT parameters to get what you need.

  • 1
    I was hoping for a way to gain access to a conceptual list to iterate through. This could be used as a key to a table whose relevant entries all have that key, I grant you - but that goes back to the early notes. I also grant that if I reduced my requirements to a fixed length tuple, your technique would be applicable. Thanks for the suggestion. – rich p Dec 28 '16 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.