-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$CREATE DEFINER=`root`@`localhost` FUNCTION `Func_CompletedANDUnfinishedTaskNumberForEmployee`(`_OperatorId` INT, `_ProjectTaskStatusIds` VARCHAR(250), `_DATEFROM` DATE,`_DATETO` DATE) RETURNS int(11)
BEGIN SET @Total = NULL;
IF _DATEFROM IS NOT NULL AND _DATEFROM <> '' AND _DATETO IS NOT NULL AND _DATETO <> '' THEN
SELECT
COUNT(ProjectTaskId) INTO @Total
FROM
ProjectTask
WHERE
OperatorId = _OperatorId AND
ProjectTaskStatusId IN (_ProjectTaskStatusIds) AND
ScheduleEndDate BETWEEN _DATEFROM AND _DATETO;
ELSE
SELECT
COUNT(ProjectTaskId) INTO @Total
FROM
ProjectTask
WHERE
OperatorId = _OperatorId AND
ProjectTaskStatusId IN (_ProjectTaskStatusIds);
END IF;
IF @Total IS NULL THEN
SET @Tatal = 0;
END IF;
RETURN @Total;
END$$这样的话我如果_DATEFROM和_DATETO传进来的是两个空值(传进来两对引号'') 那判断的时候应该怎么判断呢 好象用_DATEFROM <> '' 没用有哪位高手可以告诉我怎么写啊?
mysql> SELECT DATE_FORMAT('', '%Y-%m-%d %H:%i:%s')<=>null;
+---------------------------------------------+
| DATE_FORMAT('', '%Y-%m-%d %H:%i:%s')<=>null |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)mysql> SELECT DATE_FORMAT('', '%Y-%m-%d %H:%i:%s')
-> ;
+--------------------------------------+
| DATE_FORMAT('', '%Y-%m-%d %H:%i:%s') |
+--------------------------------------+
| NULL |
+--------------------------------------+
1 row in set, 1 warning (0.00 sec)mysql>
从上面可以得到,当给一个DATETIME类型参数赋值为''的时候,得到的其实是个NULL值
_DATEFROM is not null ,这样判断是否为空。
看4楼。
把AND _DATEFROM <> '' 和 AND _DATETO <> '' 去掉就可以了。
DELIMITER $$USE `ee`$$DROP PROCEDURE IF EXISTS `ff1`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `ff1`(dd DATE,dd1 DATE)
BEGIN
CASE WHEN LENGTH(TRIM(dd))=0 THEN
SET @aa=3;
WHEN dd IS NULL THEN
SET @aa=1;
WHEN dd IS NOT NULL THEN
SET @aa=6;
SELECT dd;
WHEN MID(dd,1,4)='0000' THEN
SET @qq=5;
ELSE
SET @aa=2;
END CASE;
END$$DELIMITER ;
看看结果: 0000-00-00
if _DATEFROM <> '0000-00-00' then
...
end if
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$CREATE DEFINER=`root`@`localhost` FUNCTION `Func_CompletedTaskNumberForEmployee`(`_OperatorId` INT, `_DATEFROM` DATE,`_DATETO` DATE) RETURNS int(11)
BEGIN SET @Total = NULL;
IF _DATEFROM <> '0000-00-00' THEN
SELECT
COUNT(ProjectTaskId) INTO @Total
FROM
ProjectTask
WHERE
OperatorId = _OperatorId AND
ProjectTaskStatusId IN (2,3,4) AND
ScheduleEndDate BETWEEN _DATEFROM AND _DATETO;
ELSE
SELECT
COUNT(ProjectTaskId) INTO @Total
FROM
ProjectTask
WHERE
OperatorId = _OperatorId AND
ProjectTaskStatusId IN (2,3,4);
END IF;
IF @Total IS NULL THEN
SET @Tatal = 0;
END IF;
RETURN @Total;
END$$我这样写的话 调用的时候从外面传进来''还是过不去
怎么得到结论的,你只判断了_DATEFROM,_DATETO没有判断,
你先运行8楼的代码,看看结果,主要是日期格式
DELIMITER $$USE `ee`$$DROP PROCEDURE IF EXISTS `ffa`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `ffa`(dd DATE,dd1 DATE)
BEGIN
SELECT dd,dd1;
END$$DELIMITER ;
CALL ffa('','')
dd dd1
0000-00-00 0000-00-00
CREATE DEFINER=`root`@`localhost` FUNCTION `Func_CompletedANDUnfinishedTaskNumberForEmployee`(`_OperatorId` INT, `_ProjectTaskStatusIds` VARCHAR(250), `_DATEFROM` DATE,`_DATETO` DATE) RETURNS int(11)
BEGIN
set @total=_DATEFROM;
RETURN @Total;
END;
select Func_CompletedANDUnfinishedTaskNumberForEmployee(1,'a','','');得到的是0
DROP FUNCTION IF EXISTS Func_CompletedANDUnfinishedTaskNumberForEmployee$$
CREATE FUNCTION `Func_CompletedANDUnfinishedTaskNumberForEmployee`(`_OperatorId` INT, `_ProjectTaskStatusIds` VARCHAR(250), `_DATEFROM` DATE,`_DATETO` DATE) RETURNS INT
BEGIN
SET @total=5;
IF _DATEFROM='0000-00-00' THEN
SET @total=2;
END IF;
RETURN @Total;
END ;$$
DELIMITER ;
SELECT Func_CompletedANDUnfinishedTaskNumberForEmployee(1,'a','','');Func_CompletedANDUnfinishedTaskNumberForEmployee(1,'a','','')
2
wwwb是正确的。