BEGIN
    DECLARE MEMBER_STATE VARCHAR(3);

    SET @NURSERY_ADDRESS = (SELECT NURSERY_ADDRESS FROM COOP_MEMBERS WHERE
    COOP_MEMBERS.MEMBER_ID = MEMBER_ID);

    CASE
        WHEN @NURSERY_ADDRESS LIKE '% QLD %' THEN SET EMPLOYEE_STATE = 'QLD';
        WHEN @NURSERY_ADDRESS LIKE '% VIC %' THEN SET EMPLOYEE_STATE = 'VIC';
        WHEN @NURSERY_ADDRESS LIKE '% NSW %' THEN SET EMPLOYEE_STATE = 'NSW';
        WHEN @NURSERY_ADDRESS LIKE '% NT %' THEN SET EMPLOYEE_STATE = 'NT';
        WHEN @NURSERY_ADDRESS LIKE '% WA %' THEN SET EMPLOYEE_STATE = 'WA';
        WHEN @NURSERY_ADDRESS LIKE '% ACT %' THEN SET EMPLOYEE_STATE = 'ACT';
        WHEN @NURSERY_ADDRESS LIKE '% SA %' THEN SET EMPLOYEE_STATE = 'SA';
        WHEN @NURSERY_ADDRESS LIKE '% TAS %' THEN SET EMPLOYEE_STATE = 'TAS';
        ELSE
        SET MEMBER_STATE = NULL;
    END CASE;

    RETURN MEMBER_STATE;

END//