-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$CREATE DEFINER=`root`@`` PROCEDURE `ProjectTask_GetWorkingNember`(
_OperatorId INT,
_ProjectTaskStatusId INT,
_SelectDateFilter VARCHAR(250),
OUT _Total INT
)
BEGIN SET @Total = 0 ;
SET @WhereClause = "";
IF _SelectDateFilter = '本日' THEN
SET @WhereClause = ' ScheduleEndDate= CURDATE() ';
ElSE IF _SelectDateFilter = '本周' THEN
SET @WhereClause = ' WEEKOFYEAR(CURDATE()) = WEEKOFYEAR(ScheduleEndDate)';
ElSE IF _SelectDateFilter = '本月' THEN
SET @WhereClause = ' MONTH(ScheduleEndDate) = MONTH(CURDATE()) AND YEAR(ScheduleEndDate) = YEAR(CURDATE())';
ElSE IF _SelectDateFilter = '本季度' THEN
SET @WhereClause = ' QUARTER(ScheduleEndDate) = QUARTER(CURDATE())';
ElSE IF _SelectDateFilter = '本年' THEN
SET @WhereClause = ' YEAR(ScheduleEndDate) = YEAR(CURDATE())';
END IF;
SET @SqlStr = 'SELECT count(ProjectTaskId) INTO @Total FROM ProjectTask';
IF _SelectDateFilter IS NOT NULL AND _SelectDateFilter <> '' THEN
SET @SqlStr = CONCAT(@SqlStr,' WHERE OperatorId = ', _OperatorId,' AND ProjectTaskStatusId = ', _ProjectTaskStatusId, ' AND ', @WhereClause);
ELSE
SET @SqlStr = CONCAT(@SqlStr,' WHERE OperatorId = ', _OperatorId,' AND ProjectTaskStatusId = ', _ProjectTaskStatusId);
END IF;
SET _Total = @Total;END$$这个存储过程报错。。不知道哪里错了 应该怎么改??
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$CREATE DEFINER=`root`@`` PROCEDURE `ProjectTask_GetWorkingNember`(
_OperatorId INT,
_ProjectTaskStatusId INT,
_SelectDateFilter VARCHAR(250),
OUT _Total INT
)
BEGIN SET @Total = 0 ;
SET @WhereClause = "";
IF _SelectDateFilter = '本日' THEN
SET @WhereClause = ' ScheduleEndDate= CURDATE() ';
ElSE IF _SelectDateFilter = '本周' THEN
SET @WhereClause = ' WEEKOFYEAR(CURDATE()) = WEEKOFYEAR(ScheduleEndDate)';
ElSE IF _SelectDateFilter = '本月' THEN
SET @WhereClause = ' MONTH(ScheduleEndDate) = MONTH(CURDATE()) AND YEAR(ScheduleEndDate) = YEAR(CURDATE())';
ElSE IF _SelectDateFilter = '本季度' THEN
SET @WhereClause = ' QUARTER(ScheduleEndDate) = QUARTER(CURDATE())';
ElSE IF _SelectDateFilter = '本年' THEN
SET @WhereClause = ' YEAR(ScheduleEndDate) = YEAR(CURDATE())';
END IF;
SET @SqlStr = 'SELECT count(ProjectTaskId) INTO @Total FROM ProjectTask';
IF _SelectDateFilter IS NOT NULL AND _SelectDateFilter <> '' THEN
SET @SqlStr = CONCAT(@SqlStr,' WHERE OperatorId = ', _OperatorId,' AND ProjectTaskStatusId = ', _ProjectTaskStatusId, ' AND ', @WhereClause);
ELSE
SET @SqlStr = CONCAT(@SqlStr,' WHERE OperatorId = ', _OperatorId,' AND ProjectTaskStatusId = ', _ProjectTaskStatusId);
END IF;
SET _Total = @Total;END$$这个存储过程报错。。不知道哪里错了 应该怎么改??
_OperatorId INT,
_ProjectTaskStatusId INT,
_SelectDateFilter VARCHAR(250),
OUT _Total INT
)
BEGIN SET @Total = 0 ;
SET @WhereClause = "";
IF _SelectDateFilter = '本日' THEN
SET @WhereClause = ' ScheduleEndDate= CURDATE() ';
ElSEIF _SelectDateFilter = '本周' THEN
SET @WhereClause = ' WEEKOFYEAR(CURDATE()) = WEEKOFYEAR(ScheduleEndDate)';
ElSEIF _SelectDateFilter = '本月' THEN
SET @WhereClause = ' MONTH(ScheduleEndDate) = MONTH(CURDATE()) AND YEAR(ScheduleEndDate) = YEAR(CURDATE())';
ElSEIF _SelectDateFilter = '本季度' THEN
SET @WhereClause = ' QUARTER(ScheduleEndDate) = QUARTER(CURDATE())';
ElSEIF _SelectDateFilter = '本年' THEN
SET @WhereClause = ' YEAR(ScheduleEndDate) = YEAR(CURDATE())';
END IF;
SET @SqlStr = 'SELECT count(ProjectTaskId) INTO @Total FROM ProjectTask';
IF _SelectDateFilter IS NOT NULL AND _SelectDateFilter <> '' THEN
SET @SqlStr = CONCAT(@SqlStr,' WHERE OperatorId = ', _OperatorId,' AND ProjectTaskStatusId = ', _ProjectTaskStatusId, ' AND ', @WhereClause);
ELSE
SET @SqlStr = CONCAT(@SqlStr,' WHERE OperatorId = ', _OperatorId,' AND ProjectTaskStatusId = ', _ProjectTaskStatusId);
END IF;
SET _Total = @Total;END$$