Sometimes there is need to use conditional expression inside stored procedure to control the flow of execution.
We can use IF or CASE statements for this.


Below is a stored procedure to check the performance of a student based on its score.
Store the below stored procedure in a file called get_performance.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS get_performance$$

CREATE PROCEDURE get_performance
    (score NUMERIC(8, 2),
    OUT result VARCHAR(11))

BEGIN
    IF (score >= 90) THEN
        SET result = 'OUTSTANDING';
    ELSEIF (score >= 70 …

I got sidetracked today playing with Magic Squares and thought it might be a good chance to give an example of using MySQL Routines , Loops and IF checks.

So If you are unaware of what a Magic Square is I have included a few links. It might save you a Google search but otherwise think Sudoku as an example.

• http://en.wikipedia.org/wiki/Melencolia_I
• http://en.wikipedia.org/wiki/File:Albrecht_D%C3%BCrer_-_Melencolia_I_%28detail%29.jpg
• http://www.mathematische-basteleien.de/magsquare.htm
• …

In many programing languages it is possible to shorten if statements using what’s called the ternary operator. It is sometimes referred as the “one line if statement” or the “short if statement”. This can help at times to produce cleaner code, however use this operator wisely as it is not always best to be used for more complicated statements.

PHP Example of an if statement

1
2
3
4
5
6
7
8

if($nFoo > 0)
{
   echo "I'm at the work.";
}
else
{
   echo "I'm at home.";
}

PHP Example using the ternary operator

1

      echo $nFoo > 0 ? "I'm at the work." : "I'm at home.";

The expression (expr1) ? (expr2) : (expr3) evaluates to expr2 if expr1 evaluates to …