0

my store procedure code is below,

BEGIN
SELECT * FROM email_template_punit_master WHERE punit_id = p_punit_id;

        SELECT  'SUITE' AS array_name
        FROM dp__view_punit_master as a
        WHERE a.punit_id = p_punit_id;
END

my code in lumen to call this store procedure is below,

$result = DB::select('call '.env('DB_PREFIX').'mystoreprocedure('.$parameter.')');

it gives me result of only first query from store procedure not for second query,

if I change the order of query as below,

BEGIN
SELECT  'SUITE' AS array_name
            FROM dp__view_punit_master as a
            WHERE a.punit_id = p_punit_id;
    SELECT * FROM email_template_punit_master WHERE punit_id = p_punit_id;    

    END

again it gives me result of first query instead of both the query result,

if I execute in mysql database it give me required result but call from lumen it gives first query result,

how to get all query result with lumen store procedure

hetal gohel
  • 335
  • 9
  • 21
  • You can't. Don't put trivial select statement in stored procedures, its not worth it in terms of readability or maintainability (or performance). – danblack Dec 12 '18 at 10:32
  • but it is already exist in database , it is used in live site, I can not change it, I have to manage it wih my code :( – hetal gohel Dec 12 '18 at 10:37
  • It doesn't give the right result. It cannot give the right result. Accept that you can change it by using queries in your code. Alternately accept the partial result returned. It is recalcitrant to continue to belligerently attempt to make stored procedures work, especially in the way you ask. – danblack Dec 12 '18 at 10:42
  • last option for me to use it spearate queries at code level – hetal gohel Dec 12 '18 at 10:49
  • @danblack It's perfectly possible to fetch multiple result sets from a procedure - see https://stackoverflow.com/questions/1683794/retrieving-multiple-result-sets-with-stored-procedure-in-php-mysqli . This is shown using raw mysqli or PDO. I don't know the Laravel/Lumen (or whatever the code above is) DB libraries specifically but I would hope they would provide support for that, it's a fairly common kind of requirement. – ADyson Dec 12 '18 at 11:16
  • @danblack having said that, someone has actually provided a ready-to-go function to do just that: https://stackoverflow.com/questions/21695287/how-to-fetch-multiple-result-set-from-a-mysql-stored-procedure-in-laravel/28043584#28043584 – ADyson Dec 12 '18 at 11:19
  • let me try it with lumen – hetal gohel Dec 12 '18 at 11:19
  • Possible duplicate of [how to fetch multiple result set from a mysql stored procedure in laravel](https://stackoverflow.com/questions/21695287/how-to-fetch-multiple-result-set-from-a-mysql-stored-procedure-in-laravel) – ADyson Dec 12 '18 at 11:20
  • in lumen was not working with my current library, I have written totally php code here as per you suggestion of above given link, it works – hetal gohel Dec 12 '18 at 11:30

0 Answers0