MySQL Stored Procedure with Multiple Select statements From Different Tables

Question

I'm trying to do multiple selects from different tables in a mysql stored procedure as follows

DELIMITER //  
CREATE PROCEDURE `NovemberSummary`(IN `branch` VARCHAR(60), IN `year` INT) NOT 
DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 
BEGIN
select sum(sales.amount) as Sales from sales where month (sales.date)= 11 and 
sales.branch = branch;
select sum(expenses.amount) as Expenses from expenses where month(expenses.date)= 11
and expenses.branch = branch;
END

But It returns only The first Select, as In result set only contains Sales Column.

MySQL Version is 5.6.11 - MySQL Community Server

You will need to request the subsequent resultset using your API. — eggyal
– eggyal, Commented Dec 1, 2013 at 22:27

krokodilko · Accepted Answer · 2013-12-02 11:03:22Z

7

Try this approach:

DELIMITER //  
CREATE PROCEDURE `NovemberSummary`(IN `branch` VARCHAR(60), IN `year` INT) NOT 
DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 
BEGIN
SELECT 
   ( select sum(sales.amount) from sales 
     where month (sales.date)= 11 and sales.branch = branch ) as Sales ,
   ( select sum(expenses.amount) from expenses 
     where month(expenses.date)= 11 and expenses.branch = branch ) as Expenses 
   ;
END

this procedure returns only one resultset that contains two columns: Sales + Expenses:

+-------+----------+
| Sales | Expenses |
+-------+----------+
|    20 |       15 |
+-------+----------+

, instead of two resultsets with only one column.

+-------+
| Sales |
+-------+
|    20 |
+-------+

+----------+
| Expenses |
+----------+
|       15 |
+----------+

answered Dec 2, 2013 at 11:03

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

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

1 Comment

Sohel Pathan Over a year ago

It would not work in case you fetch 2 or more than 2 columns in a SELECT statement. It would give error "Operand should contain 1 column(s)". How to handle those cases ?

Bembo · Accepted Answer · 2014-09-24 17:17:58Z

2

CREATE PROCEDURE get_data ()
BEGIN
  SELECT Code, Name, Population, Continent FROM Country
    WHERE Continent = 'Oceania' AND Population < 10000;
  SELECT Code, Name, Population, Continent FROM Country
    WHERE Continent = 'Europe' AND Population < 10000;
  SELECT Code, Name, Population, Continent FROM Country
    WHERE Continent = 'North America' AND Population < 10000;
END;

answered Sep 24, 2014 at 17:17

Bembo

1,9591 gold badge13 silver badges7 bronze badges

1 Comment

Sohel Pathan Over a year ago

This would not give what asked in question. MARIA DB PROCEDURE with above query will return output of 1st SELECT statement only. While question is asking for 2 output tables in result. In MARIA DB ";" (semi-colon) is considered as statement termination operator.

Shaan Ansari · Accepted Answer · 2019-11-22 12:02:15Z

0

Try This

CREATE PROCEDURE `NovemberSummary`(IN `branch` VARCHAR(60), IN `year` INT) NOT 

DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 

BEGIN

DECLARE SalesAmount VARCHAR(255) DEFAULT 0;
DECLARE ExpensesAmount VARCHAR(255) DEFAULT 0;

SELECT SUM(sales.amount) AS Sales INTO SalesAmount FROM sales WHERE MONTH (sales.date)= 11 AND 

sales.branch = branch;

SELECT SUM(expenses.amount) AS Expenses INTO ExpensesAmount FROM expenses WHERE MONTH(expenses.date)= 11

AND expenses.branch = branch;

SELECT SalesAmount AS Sales, ExpensesAmount AS Expenses;

END

answered Nov 22, 2019 at 12:02

Shaan Ansari

5707 silver badges11 bronze badges

Comments

Engr. Khuram Shahzad · Accepted Answer · 2020-08-27 03:57:50Z

0

Using Multiple Select Statement in Stored Procedures

Try with the below query.

    select distinct ProdSubCategory, NULL,NULL
from Table1
inner join Table2 on ID_FK= ID_PK
where Table2.Type=@chvType and Table1.ProdCategory=@chvProdSubCategory

UNION ALL

select NULL, Brand,NULL
from Table1
inner join Table2 on ID_FK= ID_PK
where Table2.Type=@chvType and Table1.ProdCategory=@chvProdSubCategory

UNION ALL
select NULL, Price,NULL
from Table1
inner join Table2 on ID_FK= ID_PK
where Table2.Type=@chvType and Table1.ProdCategory=@chvProdSubCategor

answered Aug 27, 2020 at 3:57

Engr. Khuram Shahzad

4851 gold badge5 silver badges17 bronze badges

Comments

Sonu Chohan · Accepted Answer · 2021-12-14 11:20:55Z

BEGIN
DELETE FROM ut_axis_balance WHERE oth_refer = 'CLGBAL';
UPDATE ut_axis_karvy SET mfund_code = '200' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '039' );
UPDATE ut_axis_karvy SET mfund_code = '160' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '106' );
UPDATE ut_axis_karvy SET mfund_code = '128' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '107' );
UPDATE ut_axis_karvy SET mfund_code = '171' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '123' );
UPDATE ut_axis_karvy SET mfund_code = '193' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '119' );
UPDATE ut_axis_karvy SET mfund_code = '193' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '121' );
UPDATE ut_axis_karvy SET mfund_code = '193' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '202' );
UPDATE ut_axis_karvy SET mfund_code = '161' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '120' );
UPDATE ut_axis_karvy SET mfund_code = '160' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '166' );
UPDATE ut_axis_karvy SET mfund_code = '160' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '167' );
UPDATE ut_axis_karvy SET mfund_code = '160' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '175' );
UPDATE ut_axis_karvy SET mfund_code = '042' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '200' );
UPDATE ut_axis_karvy SET mfund_code = '193' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '202' );
UPDATE ut_axis_karvy SET mfund_code = '161' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '266' );
UPDATE ut_axis_karvy SET mfund_code = '193' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '495' );
UPDATE ut_axis_karvy SET mfund_code = '177' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '003' );
UPDATE ut_axis_karvy SET mfund_code = '177' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '132' );
UPDATE ut_axis_karvy SET mfund_code = '177' where mfund_code IN ( SELECT mfund_code FROM ut_axis_karvy WHERE mfund_code = '144' );
END

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

Collectives™ on Stack Overflow

MySQL Stored Procedure with Multiple Select statements From Different Tables

5 Answers 5

1 Comment

1 Comment

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related