MySQL stored procedure is not returning any output . but returning when execute it as a first query as a single value

Question

Mysql stored procedure is not returning any out put wnen execute as a stored procedure. but when i execute as separate sql statement for every out parameter its returning output . can u please help me the reason as i am new to this mySQL .

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ShopBusinessFunctions`(IN `shopid` VARCHAR(15), OUT `referalcount` INT, OUT `starscount` INT, OUT `WalletBalance` DECIMAL(10,2), OUT `TodaysBusiness` DECIMAL(10,2), OUT `CurMonthBusiness` DECIMAL(10,2), OUT `CurYearBusiness` DECIMAL(10,2))
BEGIN
  DECLARE v_done INT DEFAULT FALSE;
  DECLARE v_idRoom INT;
  DECLARE v_maxAvailable INT;
  DECLARE walletamount DECIMAL(10,2);
  DECLARE toozocommission DECIMAL(10,2);
  DECLARE WALLETBALANCE DECIMAL(10,2);

SELECT walletamount = sum(Amount) FROM shopswallet where shopid=shopid and isapproved =1;

SELECT toozocommission = sum(Amount) FROM shopspurchasewallet where shopid=shopid;

set WalletBalance  = walletamount - toozocommission ;

select TodaysBusiness = sum(Amount) from shopspurchasewallet   
where date_format(date_entered, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d') and shopid=shopid;

select CurMonthBusiness = sum(Amount) from shopspurchasewallet   
where date_format(date_entered, '%Y-%m') = date_format(now(), '%Y-%m') and shopid=shopid;

select CurYearBusiness = sum(Amount) from shopspurchasewallet   
where date_format(date_entered, '%Y') = date_format(now(), '%Y') and shopid=shopid;

select count(ReferalID) from shops where ReferalID= shopid;

select starscount = count(starratings) from customerpurchaseentry where shopid =shopid;

END$$
DELIMITER ;

When i execute as single sql statement for each output parameter its returning output . Plese help me to solve this issue .

Answer 1

Instead of using OUT parameters, you can return the values in a result set. Also, instead of using SELECT variable = COUNT(*) you should use SELECT COUNT(*) into variable.

Here is a procedure with the fixes:

CREATE DEFINER=`root`@`localhost` PROCEDURE `ShopBusinessFunctions`(
in_shopid VARCHAR(15)
)
BEGIN
DECLARE v_walletamount DECIMAL(10,2);
DECLARE v_toozocommission DECIMAL(10,2);
DECLARE v_WalletBalance DECIMAL(10,2);
DECLARE v_TodaysBusiness DECIMAL(10,2);
DECLARE v_CurMonthBusiness DECIMAL(10,2);
DECLARE v_CurYearBusiness DECIMAL(10,2);
DECLARE v_ReferalID_Count int;
DECLARE v_starscount int;


SELECT sum(Amount), sum(if(isapproved=1,Amount,0) ) into v_walletamount, v_toozocommission 
FROM shopspurchasewallet 
where shopid=shopid;

set v_WalletBalance  = v_walletamount - v_toozocommission ;

select sum(Amount) into v_TodaysBusiness
from shopspurchasewallet   
where cast(date_entered as date) = current_date() and shopid=shopid;

select sum(Amount) into v_CurMonthBusiness 
from shopspurchasewallet   
where date_format(date_entered, '%Y-%m') = date_format(current_date(), '%Y-%m') and shopid=shopid;

select sum(Amount)  into v_CurYearBusiness 
from shopspurchasewallet   
where year(date_entered) = year(current_date()) and shopid=shopid;

select count(ReferalID) into v_ReferalID_Count
from shops 
where ReferalID= shopid;

select count(starratings) into v_starscount 
from customerpurchaseentry 
where shopid =shopid;

select 
  v_ReferalID_Count as 'referalcount',
  v_starscount as 'starscount',
  v_WalletBalance as 'WalletBalance',
  v_TodaysBusiness as 'TodaysBusiness',
  v_CurMonthBusiness as 'CurMonthBusiness',
  v_CurYearBusiness as 'CurYearBusiness';
  

END
$$

And you can call the procedure and fetch the results:

$sql = 'CALL ShopBusinessFunctions(:in_shopid)'; 
$stmt = $conn->prepare($sql); 
$stmt->bindParam(':in_shopid', $shopid, PDO::PARAM_STR); 
$stmt->execute(); 
$results = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt->closeCursor(); 

The $results will then hold the result for the procedure.