Conditional Control in MySQL Stored Procedure

  • Avinash
  • 1
  • May 23, 2012
  • Database

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:

  1. IF
  2. 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:

if(condition true) execute this else execute this
  if(condition true)
    execute this
  else
    execute this

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.

-- 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;
    -- 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;

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.

CASE WHEN expression THEN commands ... WHEN expression THEN commands ELSE commands END CASE;
   CASE
   WHEN expression THEN commands
   ...
   WHEN expression THEN commands
   ELSE commands
   END CASE;

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.

Related Posts

Written by Avinash

Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.

View all posts by:

  • Suren

    Great tutorial and in a very user friendly language