0

Trying to do something very simple. Using If/Then, is there a way to run a separate Select statement based on the value of a variable? The function GetTotalActiveUnits() in the below code returns an integer.

    set @RetVal =  GetTotalActiveUnits(CustomerCode);
    if  @RetVal = 0 then 
    Select * from tblREF_UnitInfo;
    else
    select * from tblREF_State;
    end if
John
  • 17
  • 6
  • 'MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs' - https://dev.mysql.com/doc/refman/8.0/en/flow-control-statements.html , stored prgrams are procedures,functions,triggers and events. – P.Salmon Jan 12 '21 at 09:38
  • Yep, I am aware of that. As shown in my example I am using the If construct. – John Jan 12 '21 at 14:33
  • IN that case there is no problem with your code and it works as coded.. – P.Salmon Jan 12 '21 at 14:34
  • Yes, I was missing a semi-colon. Other than that, it work when embedded in a SP. – John Jan 12 '21 at 18:07

1 Answers1

0

There is no problem with your code - at least as far as IF is concerned and if it is in a stored routine.

drop procedure if exists p;
delimiter $$
create procedure p(inp int)
begin
     set @RetVal = inp;# GetTotalActiveUnits(CustomerCode);
    if  @RetVal = 0 then 
     select @retval ;
    else
     select @retval ;
    end if ;

end $$

delimiter ;

call p(1);
+---------+
| @retval |
+---------+
|       1 |
+---------+
1 row in set (0.001 sec)

call p(0)
+---------+
| @retval |
+---------+
|       0 |
+---------+
1 row in set (0.001 sec)
P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • OK, I screwed up. I thought I could just paste my code into a workbench SQL statement window and run it. But it kept showing errors. I never really tried it within an actual SP. My screw up. Thanks for the help. – John Jan 12 '21 at 14:54