If statement in stored procedure sql videos in telugu 93

 https://youtu.be/eDIPxJ2oDhc

----------------------------------------------------------

MySQL : If Statement

The IF statement implements a basic conditional construct within a stored programs and must be terminated with a semicolon. There is also an IF() function, which is different from the IF statement. Here is the syntax of if statement 

IF condition THEN statement(s)   

[ELSEIF condition THEN statement(s)] ...         

[ELSE statement(s)]  

END IF

https://www.mysqltutorial.org/mysql-if-statement/

DELIMITER $$

CREATE PROCEDURE vote(IN  age INT, OUT result  VARCHAR(20))

BEGIN

IF age >= 18 THEN SET result = 'Permision ok';

ELSE SET result = 'reJected';

END IF;

END $$

DELIMITER ;

delimiter $$

create procedure vote1(in age int, out result varchar(20))

begin

if age > 18 then set result = "Eligible";

elseif age=18 then set result="he can apply";

else set result="Not eligible";

end if;

end $$

delimiter ;

------------------

Enter password: *******

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use classicmodels;

Database changed

mysql> delimiter $$

mysql> create procedure vote(in age int, out result varchar(10))

    -> begin

    -> if age >= 18 then set result = "Eligible";

    -> else set result="Not eligible";

    -> end if;

    -> end $$

Query OK, 0 rows affected (0.12 sec)

mysql> delimiter ;

mysql> call vote(16 ,@res);

ERROR 1406 (22001): Data too long for column 'result' at row 1

mysql> drop procedure vote;

Query OK, 0 rows affected (0.15 sec)

mysql> delimiter $$

mysql> create procedure vote(in age int, out result varchar(20))

    -> begin

    -> if age >= 18 then set result = "Eligible";

    -> else set result="Not eligible";

    -> end if;

    -> end $$

Query OK, 0 rows affected (0.14 sec)

mysql> delimiter ;

mysql> call vote(16,@res);

Query OK, 0 rows affected (0.00 sec)

mysql> select @res;

+--------------+

| @res         |

+--------------+

| Not eligible |

+--------------+

1 row in set (0.00 sec)

mysql> call vote(20,@res);

Query OK, 0 rows affected (0.00 sec)

mysql> select @res;

+----------+

| @res     |

+----------+

| Eligible |

+----------+

1 row in set (0.00 sec)

mysql> delimiter $$

mysql> create procedure vote1(in age int, out result varchar(20))

    -> begin

    -> if age > 18 then set result = "Eligible";

    -> elseif age=18 then set result="he can apply";

    -> else set result="Not eligible";

    -> end if;

    -> end $$

Query OK, 0 rows affected (0.14 sec)

mysql> delimiter ;

mysql> call vote1(19,@res);

Query OK, 0 rows affected (0.02 sec)

mysql> select @res;

+----------+

| @res     |

+----------+

| Eligible |

+----------+

1 row in set (0.00 sec)

mysql> call vote1(18,@res);

Query OK, 0 rows affected (0.00 sec)

mysql> select @res;

+--------------+

| @res         |

+--------------+

| he can apply |

+--------------+

1 row in set (0.00 sec)

mysql> call vote1(16,@res);

Query OK, 0 rows affected (0.00 sec)

mysql> select @res;

+--------------+

| @res         |

+--------------+

| Not eligible |

+--------------+

1 row in set (0.00 sec)

mysql>

-------------

select customerNumber,creditlimit from customers limit 20;

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(

    IN  pCustomerNumber INT, 

    OUT pCustomerLevel  VARCHAR(20))

BEGIN

    DECLARE credit DECIMAL DEFAULT 0;

    SELECT creditLimit 

    INTO credit

    FROM customers

    WHERE customerNumber = pCustomerNumber;

    IF credit > 50000 THEN

        SET pCustomerLevel = 'PLATINUM';

    ELSEIF credit <= 50000 AND credit > 10000 THEN

        SET pCustomerLevel = 'GOLD';

    ELSE

        SET pCustomerLevel = 'SILVER';

    END IF;

END $$

DELIMITER ;

CALL GetCustomerLevel(447, @level); 

SELECT @level;