CODEWITHSUNDEEP
mysqlstored-procedures
wolfsburg18
wolfsburg18

Reputation: 13

MySQL Procedure with IN date fails

First time poster really appreciate any assistance.

I am completely stuck on an issue with MySQL stored procedures and completely green to them.

Here is the procedure as it is currently written, MySQL accepts it without any issue so unless there is a syntax error, the procedure is written correctly.

However when I call the procedure call test (2011-04-01, 2011-04-07); no results are returned, yet the select statement works just fine.

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;

Upvotes: 1

Views: 19708

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

Don't quote the variables, otherwise it is an invalid date (the string '@xxx' cannot convert to a date).
Don't backtick the parameters
You are also returning TestStatus 3 times... crazy stuff!

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,
    @cache := CASE
        WHEN results = 'pass' THEN '1'
        WHEN results = 'fail' THEN '0'
        ELSE '-1'
    END AS TestStatus,
    @cache AS TestStatus, # 2nd time ?
    @cache AS TestStatus  # 3rd time ??
from completed
WHERE date >= StartDate and date <= EndDate;
END;

Upvotes: 0

Jon Black
Jon Black

Reputation: 16559

quick tidy up - i'll leave it to '@You' to SPOT the 'differences' and there are more than '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());

Upvotes: 2

Nicola Cossu
Nicola Cossu

Reputation: 56367

Dates must be passed within quotes

call test ('2011-04-01', '2011-04-07');

edit. Why do you call test if your stored procedure is named NewProc ?

Upvotes: 0

Related Questions