CODEWITHSUNDEEP
mysqlstored-procedures
Anonymous
Anonymous

Reputation: 499

How to create a Simple Procedure using ONLY INOUT parameter but Not IN, OUT separately and then How CALL them

Question is: Find the highest commission (comm) from emp table and give the person with the lowest commission the same commission as the person with the high commission

emp Table is: 

 empno, ename, job, mgr, hiredate, sal, comm, deptno
'1', 'MrBelow1000', 'Entry Level', '100', '2015-10-02', '900.00', '1350.00', '1'
'2', 'MrBelow1500', 'Mid Level', '101', '2015-11-02', '1250.00', '1350.00', '2'
'3', 'MrBelow2000', 'UpMid Level', '102', '2015-11-12', '1600.00', '1350.00', '2'
'4', 'MrSalNull', 'Bum', '102', '2015-07-02', NULL, '1200.00', '2'
'5', 'MrRich', 'President', '0', '2014-10-02', '99900.00', '0.00', '1'

Problem is that I want to know how to use only INOUT (NOT IN OUT) parameter to get the highest commission and then How to call INOUT parameter.

I know in IN OUT we pass some IN value to IN variable and OUT value comes out but how about INOUT? Thanks and appreciated

Here is my procedure: 

DELIMITER //
CREATE PROCEDURE max_Comission ( INOUT max_Comission_Var DECIMAL(10,2))

BEGIN
SELECT MAX(comm) INTO max_Comission_Var
FROM emp ;

END //

Here is how I am calling 

CALL max_Comission(900 @test);
SELECT (@test)

Upvotes: 0

Views: 45

Answers (1)

wchiquito
wchiquito

Reputation: 16551

Try:

mysql> DELIMITER //

mysql> DROP PROCEDURE IF EXISTS `max_Comission`//
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> CREATE PROCEDURE `max_Comission` (
                  INOUT `max_Comission_Var` DECIMAL(10, 2)
       )
    -> BEGIN
    ->   /*SELECT MAX(comm) INTO max_Comission_Var
   /*>   FROM emp;*/
    -> 
    ->   SELECT `max_Comission_Var`;
    ->   SELECT 1000 INTO `max_Comission_Var`;
    -> END//
Query OK, 0 rows affected (0,00 sec)

mysql> DELIMITER ;

mysql> SET @`value` := 900;
Query OK, 0 rows affected (0,00 sec)

mysql> CALL `max_Comission`(@`value`);
+---------------------+
| `max_Comission_Var` |
+---------------------+
|              900.00 |
+---------------------+
1 row in set (0,00 sec)

Query OK, 1 row affected (0,00 sec)

mysql> SELECT @`value`;
+----------+
| @`value` |
+----------+
|  1000.00 |
+----------+
1 row in set (0,00 sec)

Upvotes: 1

Related Questions