-- Collection of code snippets by Arne Vajhøj 
-- (likely posted to comp.os.vms/INFO-VAX, INFO-TPU, MACRO32, eksperten.dk, newz.dk, LinkedIn or other place sometime between 1990 and now) 
CREATE TABLE qeportal (serial VARCHAR(20), active CHAR(1));
INSERT INTO qeportal VALUES('12345678901234567890', 'y');
DELIMITER //
CREATE PROCEDURE checkserial(_serial varchar(20))
BEGIN
    SELECT CASE WHEN active IS NULL THEN 2 WHEN active = 'y' THEN 1 WHEN active = 'n' THEN 3 ELSE 2 END AS userstatus FROM qeportal WHERE serial = _serial;
END//
DELIMITER ;
CALL checkserial('12345678901234567890');
CALL checkserial('');
DELIMITER //
CREATE PROCEDURE checkserial2(_serial varchar(20))
BEGIN
    DECLARE n INTEGER;
    SELECT COUNT(*) INTO n FROM qeportal WHERE serial = _serial;
    IF n > 0 THEN
        SELECT CASE WHEN active IS NULL THEN 2 WHEN active = 'y' THEN 1 WHEN active = 'n' THEN 3 ELSE 2 END AS userstatus FROM qeportal WHERE serial = _serial;
    ELSE
        SELECT 2 AS userstatus;
    END IF;
END//
DELIMITER ;
CALL checkserial2('12345678901234567890');
CALL checkserial2('');
DROP PROCEDURE checkserial;
DROP PROCEDURE checkserial2;
DROP TABLE qeportal;
