CODEWITHSUNDEEP
mysqlsqlif-statement
HenrikM
HenrikM

Reputation: 457

Using if statements in a stored procedure in MySQL

I'm trying to create a stored procedure in MySQL with an if-elseif statement but it doesn't seem to work. I can create a procedure that will run either the if or the elseif but not with the actual switch.

CREATE DEFINER=`root`@`%` PROCEDURE `sp_do_something`(
IN setting VARCHAR(30),
IN setting2 VARCHAR(30),
IN if_else_switch VARCHAR(20))

DETERMINISTIC
IF @if_else_switch = 'foo' then
BEGIN 
...
END;
elseif @if_else_switch = 'bar' then
BEGIN
...
END; 
END IF;

Then I call it with 

CALL `database`.`sp_do_something`('setting', 'setting2', 'foo');

It completes without any errors but just returns "Affected rows: 0" and doesn't appear to be doing any of the actual SQL-code.

I'm guessing that it has something to do with the last value of the call-query and it somehow not being caught by either if - but why?

Upvotes: 0

Views: 49

Answers (1)

Vipin Jain
Vipin Jain

Reputation: 3756

Try This

CREATE DEFINER=`root`@`%` PROCEDURE `sp_do_something`(
IN setting VARCHAR(30),
IN setting2 VARCHAR(30),
IN if_else_switch VARCHAR(20))

DETERMINISTIC
IF if_else_switch = 'foo' then
......
elseif if_else_switch = 'bar' then
.......
END IF;

when you use @ with variable so it is global variable and it value persist till end of the session. and in your case if_else_switch is come parameter so it is local variable. so use the local variable instead global variable

for more know http://www.java2s.com/Tutorial/MySQL/0201__Procedure-Function/LOCALSESSIONANDGLOBALVARIABLESINMYSQL.htm

Upvotes: 3

Related Questions