Re: How to replace in syntax when calling stored procedure?

[Clement Yap]

Hi

Thank you very much. It works.

However, I would like to use function instead of procedure .

Here it goes.
Building a stored function

DELIMITER $$

DROP FUNCTION IF EXISTS `dbprodigy`.`NumOfTests` $$
CREATE FUNCTION `dbprodigy`.`NumOfTests`(yr varchar(4), mth varchar(2)) RETURNS INT

BEGIN
Declare number int default 0;
Select count(distinct SN) into number from tester where Date > Str_to_Date(concat(yr, mth, '01'), '%y%m%d') and
Date < Str_to_Date(concat(yr, mth, '01'), '%y%m%d') + interval 1 Month group by SN;
Return(number);
END $$

DELIMITER ;


Calling syntax:

select dbprodigy.NumOfTests ('2012', '02');

But the error gave out is :- "truncated incorrect date value :'20120231"
and No data - zero rows fetched, selected or processed.

Where could I have gone wrong?

Please advise. Thanks again