Mega Code Archive

 

Categories / MySQL / Procedure Function

 

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;