mysql Merging results from a stored procedure

Hi guys,

How do i merge results that are returned from a stored procedure which contains 7 diffferent result sets.

      CALL `1_cashier`(whst);
      CALL `2_register`(whst);
      CALL `3_income`(whst);
      CALL `4_Total_Money`(whst);
      CALL `5_dept`(whst);
      CALL `6_customer`(whst);
      CALL `7_totals`(whst);

END $$

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

1) do you really need to merge those results ? can't you simply use them at client side ? (usually merging is used for sorting or grouping)
2) you can imagine make the procedure fill a temprary table with data, and then make the main procedure, select those data from the temporary table
3) UNION the results from the selects, but it can't be done outside many stored procedures but inside the same.

could you develop a bit concerning what you are doing and which kind of results you need in output, your current approach is probably not the best one.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
theGhost_k8Database ConsultantCommented:
As said union is not possible with SP, you can do that with Functions.

Select function1() union
Select function2()....

Now if you convert  these SPs to functions and just make them return query string, you can UNION here in SP and execute.

this approach depends on what you're trying to achieve!! Instead of making 7 diff SPs you can do that in single SP as well!!
kingjelyAuthor Commented:
Hi kate,

These are 6 seperate reports
`1_cashier` analysis
`2_register` analysis
`3_income` analysis
`4_Total_Money` analysis
`5_deptartment analysis`
`6_customer` analysis`    and

whst = for store(s) and between date range, and a station number(whatever station number(s) in the store)

But most people run this report as
COMBINED ANALYSIS (1 - 6) they want to see the lot,

Rather then run each individual report.

but both combined, and each individual must be available.

So i guess i have got the each individual bit done, (thanks flytox)

It would be good if i could merge all 7 into one long string..

or your saying now i have each individual, just change SP for COMBINED to a stored function and combine all 7 as 1, with union ?

Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

kingjelyAuthor Commented:

Flytox, your a star,
1) I kind of need to merge the data as where the data needs to be cant read in 6 different calls(easily)
2) Fill temp table , would this just fill the columns? ( could i call this > export to excel to see the results, - this gives me hope that VB could read the whole string and place the fields on a form.
Union would be the answer to the question  flytox do you think ?

      CALL `1_cashier`(whst) union
      CALL `2_register`(whst) union
      CALL `3_income`(whst) union
      CALL `4_Total_Money`(whst) union
      CALL `5_dept`(whst) union
      CALL `6_customer`(whst) union
      CALL `7_totals`(whst) union

END $$

something like that would

export to excel the same way it would export to an app...

Thanks for putting up with me =D

1) if you code VB, have a read to
2) temporary table you fill with rows returned as output of each stored procedures, somehting like "INSERT INTO temp_table <your_big_select_here>" for every SP (not exactly, it's just the concept)

for output to excel, calling COMBINED as follows can give a nice result :

mysql -BNe "call COMBINED(whst)" > yourcsvfile.csv

that will output every results merged.
kingjelyAuthor Commented:

Hi flytox

Have got the guys to look at the VB code link you gave me.

I'm doing the SQL stuff, others are doing the VB code

I would like to get this file to Excel,

I have tried, your code but can't get it to work.
I don't exactly understand.

I copied and pasted this in the browser,
mysql -BNe "call COMBINED(whst)" > yourcsvfile.csv

or is this ment for the command line somehere

this is command line statement.

It simply executes the combined stored procedure in command line and output result into a file. The option -BNe I gave you remove table formating, columns and several other outputs.

Have a look at your client help under windows, but I guess it will look the same.
kingjelyAuthor Commented:

Hi, i am not on the server, im on a remote machine.

I have tried something like this

mysql -h hostname -u myuser --password mypassword mydatabase
-BNe "call COMBINED(whst)" >combined.csv

Now I get the .csv to my c:\ and i can go in to the file but it has just dumped what looks like the HELP index into the CSV file

Can you see what i have done wrong with my command line entry?

Also, do i maybe have to run the combined query with the (whst) parameter, then save it?
How will calling COMBINED(whst) know what the actul (whst) is?

eg my whst is
CALL `COMBINED`("`storenumber` = 2 AND `transdate` between '2010-02-01' AND '2010-02-01' ");
try that :

mysql -h hostname -u myuser -p -BNe "call COMBINED(\"storenumber = 2 AND transdate between '2010-02-01' AND '2010-02-01'\")" mydatabase

it will ask for your password.

If everything is fine, append "> conbined.csv" to output in the file
kingjelyAuthor Commented:
HI i got up to the point to addin the password,
But then got an error

access denied for my user,

where as, before i was not getting access denied, using same username and password?

Any clues?
kingjelyAuthor Commented:


Hi Kate,

This is my first SP it's a cashier analysis.
how do i make this a function, not a stored procedure, so i can try what you said above?

If you can show me this one, i can try to do it with all the others then use union?

CREATE PROCEDURE `1_cashier`(IN whst TEXT)

       SET @comb_1 = CONCAT("
        SELECT *,
         Round((profit / VALUE) * 100,2) AS margin
        FROM   (SELECT   `opername`                                                                  AS name,
                   Sum((`quantity` * `unitsell`) + ((`quantity` * `linediscount`) + `bulkdiscount`)) AS gross,
                   (`linediscount` * `quantity`) + (`bulkdiscount`)                                  AS discount,
                   Sum(`quantity` * `unitsell`)                                                      AS nett,
                   Sum(`quantity` * `gstretcontent`)                                                 AS gst,
                   Sum(`quantity` * `unitcostex`)                                                    AS cost,
                   Round(Sum((`unitsell` * `quantity`) - `bulkdiscount`) / 1.1 - Sum(`unitcostex`),
                         2) AS profit,
                   Round(Sum((`unitsell` * `quantity`) - `bulkdiscount`) / 1.1,
                         2)  AS VALUE,

            (SELECT Sum((`quantity` * `unitsell`) + (`quantity` * `linediscount`))
                    FROM history
                    WHERE",whst," AND `subtranstype` < 4 ) AS totalgross,

            (SELECT Sum((`linediscount` * `quantity`) + (`quantity` * `bulkdiscount`))
                    FROM history
                    WHERE",whst,"AND `subtranstype` < 4 ) AS totaldiscount,

            (SELECT Sum((`quantity` * `unitsell`))
                    FROM history
                    WHERE",whst,"AND `subtranstype` < 4 ) AS totalnett,

            (SELECT Sum((`quantity` * `gstretcontent`))
                    FROM history
                    WHERE",whst,"AND `subtranstype` < 4 ) AS totalGST,

            (SELECT Sum((`quantity` * `unitcostex`))
                    FROM history
                    WHERE",whst,"AND `subtranstype` < 4 ) AS totalcost
          FROM history
          WHERE", whst, " AND `subtranstype` < 4
          GROUP BY operid) AS margintable");
           PREPARE C_A_1 FROM @comb_1;
           EXECUTE C_A_1;

END $$

CALL `1_cashier`(" `storenumber` = 2 AND `transdate` BETWEEN '2010-02-01' AND '2010-02-01' ");
the user you're using must have rights.

Ensure "SHOW GRANTS myuser@host;" is returning correct values (where host is the machine you're trying to connect from).
kingjelyAuthor Commented:
Thanks GUys,

Flytox i have a new question on this topic,
instead of 1 parameter how do i call 4 per procedure

Ill ask now

in CREATE PROCEDURE statement, append parameters in paranthesis with simple comma, and when calling procedure, do the same. something like :

CREATE PROCEDURE test (param1 int, param2 int, param3 int) ....

call test (0, 2, 9);
kingjelyAuthor Commented:
Hi mate, you answered this one for me, about 1 hour ago, in a seperate  question

Thank you !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.