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

[Clement Yap]

Hi When I run these routines, function and procedure as in my previous posts with the same query. I am surprised that the result is different. Why is that? I could not figure out.
Procedure:-

DELIMITER $$

DROP PROCEDURE IF EXISTS `dbprodigy`.`numOfTests1` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `numOfTests1`(IN yr varchar(4), IN mth varchar(2))
BEGIN
SELECT
  COUNT(DISTINCT SN)
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;
END $$

DELIMITER ;

result give 53 rows but it is incorrect as it should be 84 rows


Modified Procedure:-

DELIMITER $$

DROP PROCEDURE IF EXISTS `dbprodigy`.`numOfTests1` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `numOfTests1`(IN yr varchar(4), IN mth varchar(2))
BEGIN
SELECT
  *
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;
END $$

DELIMITER ;

but when I modify it and run it. This time it is correct. Why is that?

Function:-

DELIMITER $$

DROP FUNCTION IF EXISTS `dbprodigy`.`NumOfTests` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `NumOfTests`(yr VARCHAR(4), mth VARCHAR(2)) RETURNS int(11)
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;
  RETURN (number);
END $$

DELIMITER ;

result give 53 rows, Still it is incorrect.

Please advise.