带有 IN 日期的 MySQL 过程失败

Question

第一次张贴者非常感谢任何帮助。

我完全被 MySQL 存储过程的问题困住了，对它们完全不感兴趣。

这是当前编写的过程，MySQL 可以毫无问题地接受它，因此除非存在语法错误，否则该过程是正确编写的。

但是当我调用程序时调用测试 (2011-04-01, 2011-04-07); 没有返回结果，但 select 语句工作正常。

CREATE PROCEDURE `NewProc`(IN `@StartDate` date,IN `@EndDate` date)

BEGIN

SELECT aux1, aux2, aux3, aux4, date, id, type,
    CASE
        WHEN results = 'pass' THEN '1'
        WHEN results = 'fail' THEN '0'
        ELSE '-1'
    END AS CertStatus,
    CASE
        WHEN results = 'pass' THEN '1'
        WHEN results = 'fail' THEN '0'
        ELSE '-1'
    END AS TestStatus,
    CASE
        WHEN results = 'pass' THEN '1'
        WHEN results = 'fail' THEN '0'
        ELSE '-1'
    END AS TestStatus,
    CASE
        WHEN results = 'pass' THEN '1'
        WHEN results = 'fail' THEN '0'
        ELSE '-1'
    END AS TestStatus
from completed
WHERE date >= '@StartDate' and date <= '@EndDate';
END;

Answer 1

快速整理 - 我会把它留给“@You”来找出“差异”，并且不止“1”

delimiter ;

drop procedure if exists list_test_status;

delimiter #

create procedure list_test_status
(
in p_start_date date,
in p_end_date date
)
begin

select aux1, aux2, aux3, aux4, date, id, type,
case
    when results = 'pass' then 1
    when results = 'fail' then 0
    else -1
end as CertStatus,
case
    when results = 'pass' then 1
    when results = 'fail' then 0
    else -1
end as TestStatus,
case
    when results = 'pass' then 1
    when results = 'fail' then 0
    else -1
end as TestStatus,
case
    when results = 'pass' then 1
    when results = 'fail' then 0
    else -1
end as TestStatus
from 
 completed
where 
 date >= p_start_date and date <= p_end_date;

end#

delimiter ;

call list_test_status(curdate() - interval 1 month, curdate());