SQL/MySQL/Procedure Function/Call

Call another function

   <source lang="sql">

mysql> mysql> delimiter $$ mysql> mysql> CREATE FUNCTION isodd(input_number int)

   ->        RETURNS int
   -> BEGIN
   ->         DECLARE v_isodd INT;
   ->
   ->         IF MOD(input_number,2)=0 THEN
   ->                 SET v_isodd=FALSE;
   ->         ELSE
   ->                 SET v_isodd=TRUE;
   ->         END IF;
   ->
   ->         RETURN(v_isodd);
   ->
   -> END$$

Query OK, 0 rows affected (0.01 sec) mysql> mysql> CREATE PROCEDURE myProc(aNumber int)

   -> Begin
   ->      IF (isodd(aNumber)) THEN
   ->           SELECT " is odd";
   ->      ELSE
   ->           SELECT " is even";
   ->      END IF;
   -> END$$

Query OK, 0 rows affected (0.02 sec) mysql> mysql> delimiter ; mysql> mysql> mysql> call myProc(2); +----------+ | is even | +----------+ | is even | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec) mysql> mysql> call myProc(1); +---------+ | is odd | +---------+ | is odd | +---------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> call myProc(4); +----------+ | is even | +----------+ | is even | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> drop function isodd; Query OK, 0 rows affected (0.00 sec) mysql> mysql>

       </source>
   
  

Call another procedure

   <source lang="sql">

mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc()

   -> BEGIN
   ->
   ->      SELECT "Alpha release of MySQL";
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE PROCEDURE myProc1()

   -> BEGIN
   ->
   ->      call myProc();
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> mysql> mysql> call myProc1(); +------------------------+ | Alpha release of MySQL | +------------------------+ | Alpha release of MySQL | +------------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.02 sec) mysql> drop procedure myProc1; Query OK, 0 rows affected (0.00 sec) mysql> mysql>

       </source>
   
  

Call another procedure to pass the error code

   <source lang="sql">

mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc()

   ->      MODIFIES SQL DATA
   -> BEGIN
   ->      DECLARE l_status VARCHAR(20);
   ->
   ->     CALL myProc1(l_status);
   ->        IF l_status="Duplicate Entry" THEN
   ->               SELECT CONCAT("Warning: using existing definition for location ") AS warning;
   ->      END IF;
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE PROCEDURE myProc1(OUT out_status VARCHAR(30))

   -> BEGIN
   ->    set out_status = "Duplicate Entry";
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> delimiter ; mysql> call myProc(); +--------------------------------------------------+ | warning | +--------------------------------------------------+ | Warning: using existing definition for location | +--------------------------------------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec) mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> drop procedure myProc1; Query OK, 0 rows affected (0.00 sec) mysql> mysql>

       </source>
   
  

Call another procedure with parameter

   <source lang="sql">

mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc(id int)

   -> BEGIN
   ->
   ->      SELECT "in proc"+ id;
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE PROCEDURE myProc1()

   -> BEGIN
   ->
   ->      call myProc(1);
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> mysql> call myProc1(); +---------------+ | "in proc"+ id | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> drop procedure myProc1; Query OK, 0 rows affected (0.00 sec) mysql>

       </source>