SQl 存储过程不工作 [英] SQl Stored Procedure not working

问题描述

我有一个无法正常运行的存储过程:

I have a stored procedure that wont run properly:

CREATE DEFINER=`root`@`localhost` PROCEDURE `visitor_search`(
IN _startDate datetime,
IN _endDate datetime,
IN _visitorName text,
IN _personToVisit text
)
BEGIN
SELECT * FROM visitor
where
logged_in >= _startDate
and logged_in <= _endDate
and concat(firstname, "", lastname) like('%' + _visitorName + '%')
and person_to_visit like('%' + _personToVisit + '%')
order by logged_in desc;
END

我认为是以下几行在起作用.

I think it is the following lines that are acting up.

and concat(firstname, "", lastname) like('%' + _visitorName + '%')
and person_to_visit like('%' + _personToVisit + '%')

这是正确的语法吗?

问候，

鲍勃

推荐答案

你也需要在那里使用 CONCAT().

You need to use CONCAT() there too.

and concat(firstname, "", lastname) like concat('%', _visitorName, '%')
and person_to_visit like concat('%', _personToVisit, '%')

并且不要忘记使用不同的分隔符.

and don't forget to use a different delimiter.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `visitor_search`(
IN _startDate datetime,
IN _endDate datetime,
IN _visitorName text,
IN _personToVisit text
)
BEGIN
SELECT * FROM visitor
where
logged_in >= _startDate
and logged_in <= _endDate
and concat(firstname, "", lastname) like concat('%', _visitorName, '%')
and person_to_visit like concat('%', _personToVisit, '%')
order by logged_in desc;
END $$
DELIMITER ;

这篇关于SQl 存储过程不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！