Output comparison value from a procedure : Procedure « Procedure Function « SQL / MySQL

1. SQL / MySQL
2. Procedure Function
3. Procedure

Output comparison value from a procedure

       


CREATE TABLE EmployeeS(
         EmployeeNO       INTEGER      NOT NULL,
         NAME           CHAR(15)     NOT NULL,
         INITIALS       CHAR(3)      NOT NULL,
         BIRTH_DATE     DATE                 ,
         SEX            CHAR(1)      NOT NULL,
         JOINED         SMALLINT     NOT NULL,
         STREET         VARCHAR(30)  NOT NULL,
         HOUSENO        CHAR(4)              ,
         POSTCODE       CHAR(6)              ,
         TOWN           VARCHAR(30)  NOT NULL,
         PHONENO        CHAR(13)             ,
         LEAGUENO       CHAR(4)              ,
         PRIMARY KEY    (EmployeeNO)           );
         

CREATE   TABLE PENALTIES
        (PAYMENTNO      INTEGER      NOT NULL,
         EmployeeNO       INTEGER      NOT NULL,
         PAYMENT_DATE   DATE         NOT NULL,
         AMOUNT         DECIMAL(7,2) NOT NULL,
         PRIMARY KEY    (PAYMENTNO)          );
         
                  
DELIMITER $$

CREATE PROCEDURE LARGEST
   (OUT T CHAR(10))
BEGIN
   IF (SELECT COUNT(*) FROM EmployeeS) >
      (SELECT COUNT(*) FROM PENALTIES) THEN
      SET T = 'EmployeeS';
   ELSEIF (SELECT COUNT(*) FROM EmployeeS) =
          (SELECT COUNT(*) FROM PENALTIES) THEN
      SET T = 'EQUAL';
   ELSE
      SET T = 'PENALTIES';
   END IF;
END$$


DELIMITER ;


drop table EmployeeS;
drop table penalties; 
drop procedure LARGEST;

   
    
    
    
    
    
    
  

Related examples in the same category