Using if statements in a stored procedure in MySQL

Question

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?

Vipin Jain · Accepted Answer · 2016-01-11 12:25:09Z

3

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

edited Jan 11, 2016 at 12:25

answered Jan 11, 2016 at 12:19

Vipin Jain

3,77618 silver badges36 bronze badges

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

1 Comment

HenrikM Over a year ago

That was the issue. Thank you :)

Collectives™ on Stack Overflow

Using if statements in a stored procedure in MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related