Hello guys, here goes the third article of this series. If you are not sure about this article series then I would recommonded you to start from the first article in this series. Now let’s have quick flashback of the past articles.
Flashback
In first article we have covers basics of the stored procedure which you can find here. And in second article we have covered the Parameters in stored procedure which are IN, OUT and INOUT. You can have this article to know more about this here.
In this Article
In this today’s article we will see Conditional Controls in MySQL Stored Procedure. MySQL has two types of Conditinal Controls for Stored Procedure. Which are listed as below:
- IF
- CASE
Main porpose of these conditional control is to execute query based on the value of the parameter. It will check for the value and if expression is true then it will execute the command.
Now let’s dig into each of the Conditional Controls.
IF – MySQL Stored Procedure
IF behaves same as in other programming languages. Basic logic for IF is as below:
[cc lang=”php”]
if(condition true)
execute this
else
execute this
[/cc]
Now let’s see how we can use IF in MySQL Stored Procedure.
There are three different ways in which we can write IF statement using ELSE and ELSEIF, have a look at the below code block for the same.
[cc lang=”mysql”]
— Simple IF
IF expression THEN commands
END IF;
— IF with ELSE
IF expression THEN commands
ELSE commands
END IF;
— IF with ELSEIF and ELSE
IF expression THEN commands
ELSEIF expression THEN commands
ELSE commands
END IF;
[/cc]
We can also perform the nesting of the IF inside the IF but its totally based on the requirements. :)
CASE – MySQL Stored Procedure
CASE can be used when you have large number of possibility in that case it is not good to use the IF statement so CASE is the best way to handle those conditions. Have a look at the below code block for the same.
One of the main benefit of using CASE is that, it will make your code clean and easily understandable rather than complex loop nesting.
[cc lang=”mysql”]
CASE
WHEN expression THEN commands
…
WHEN expression THEN commands
ELSE commands
END CASE;
[/cc]
CASE makes your code clean.
Conclusion
So now we are done with the third article and Basic of MySQL Stored Procedure, Parameters in MySQL Stored Procedure and Conditional Controls in MySQL stored procedure. Hope you are enjoying these article series. Share your thoughts on this article series by commenting here. Also you can share and/or request a article any time.
Don’t forget to Subscribe to our feed, Like us on Facebook and Follow us on Twiiter to get updates for the new posts/article.
