1
BEGIN
    DECLARE CheckDate INT;

    SELECT count(id)
    INTO CheckDate
    FROM test.schedule
    WHERE 'schedule_start_time' BETWEEN (
                    SELECT start_time
                    FROM test.schedule
                    WHERE bus = busid
                        AND DATE (start_time) = DATE ('schedule_start_time')
                    )
            AND (
                    SELECT end_time
                    FROM test.schedule
                    WHERE bus = busid
                        AND DATE (end_time) = DATE ('schedule_start_time')
                    );

    IF CheckDate < 1 then
        INSERT INTO `test`.`schedule` (
            `start_time`
            ,`end_time`
            ,`bus`
            )
        VALUES (
            'schedule_start_time'
            ,'schedule_end_time'
            ,busid
            );
END

IF ;END

I am testing how not to add the duplicate schedule for buses. I want to check that with MySQL stored procedure. but it does not work and add 0 values to datetime fields.

munsifali
  • 1,732
  • 2
  • 24
  • 43
  • Sql is not php. Variable names are not resolved within a string. Btw a simple unique constraint would do, you do not need the stored proc – Shadow Jun 26 '18 at 05:24

0 Answers0