SQL Function and Procedure Calls for Room Availability

RoomsAvail.sql

-- Get Rooms Until As a Function (REVISED, now takes time, AND SPECIFIC DATE)
DROP FUNCTION IF EXISTS RoomAvailableOnUntil;
DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `RoomAvailableOnUntil`(roomNum VARCHAR(255),
    nowTime VARCHAR(255), nowDate DATE) RETURNS time
BEGIN
DECLARE nextTime TIME;
 
SELECT MIN(startTime) INTO nextTime
    FROM nsccSchedule
    WHERE room = roomNum
 
    AND (DATE(nowDate) > startDate
    AND DATE(nowDate) < endDate)
 
    AND days LIKE CONCAT('%',
    (
        SELECT dayChar
        FROM daysLU
        WHERE id = DAYOFWEEK(nowDate)
    ), '%')
 
-- Input time should be like so: '1815'
    AND startTime > TIME(STR_TO_DATE(nowTime, '%H%i'));
 
RETURN nextTime;
END//
DELIMITER ;
 
 
 
-- Get Rooms avail Until in a Procedure as Batch (calls Function) using a set DATE
DROP PROCEDURE IF EXISTS RoomAvailOnUntilBatch;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `RoomAvailOnUntilBatch`(
IN campus VARCHAR(15), IN building VARCHAR(15),
IN timeStr VARCHAR(255), IN onDateStr VARCHAR(9), IN roomType VARCHAR(255))
BEGIN
DECLARE OnDate DATE;
SET OnDate = STR_TO_DATE(onDateStr, '%Y%m%d');
 
 
IF roomType IS NULL THEN
    SET roomType = '';
END IF;
IF OnDate IS NOT NULL THEN
 
    SELECT r.Room, RoomAvailableOnUntil(r.Room, timeStr, onDate) as AvailUntil
    FROM Rooms r
    WHERE r.Campus = campus
    AND r.Building = building
    AND r.RoomType LIKE CONCAT(roomType, '%')
    AND room NOT IN(
        SELECT DISTINCT room FROM nsccSchedule
        WHERE days LIKE CONCAT('%',(
          SELECT dayChar
          FROM daysLU
          WHERE id = DAYOFWEEK(onDate)
          ), '%')
        AND
          (TIME(STR_TO_DATE(timeStr,'%H%i')) >= startTime
          AND TIME(STR_TO_DATE(timeStr,'%H%i')) < endTime)
        AND
          (onDate > startDate
          AND onDate < endDate)
      );
END IF;
END//
DELIMITER;
<end,>
</end,>