mysql - 在MySQL中的查询中选择存储过程值

Question

我发现了很多与此接近的线程，但没有完全回答我的问题，所以这里是。

我正在编写一份报告，需要使用我声明为 SELECT 字段之一的变量。

这是存储过程代码: 如果存在则删除程序 sp_Test

CREATE PROCEDURE `sp_Test`(facilityId INT, startDate DATE, endDate DATE)
BEGIN

DECLARE facilityName VARCHAR(256);

SELECT facility_name INTO facilityName FROM `reporting`.`facilities` WHERE `reporting`.`facilities`.facility_id = facilityId;

SELECT `reporting`.`error_stratification`.description AS Stratification, facilityName
...Rest of query that is not important

END

唯一的问题是它将字符串“facilityName”放入我的查询结果中，而不是它的值。

有没有办法强制它在那里输出值？

Answer 1

我稍微清理了你的代码 - 下面应该提供一个很好的起点，你可以从这里继续......

drop procedure if exists sp_test;

delimiter #

create procedure sp_test(
in p_facility_id int unsigned, -- p prefix = parameter
in p_start_date date,
in p_end_date date
)
begin

declare v_facility_name varchar(255) default null; -- v prefix = variable

select f.facility_name into v_facility_name from facilities f where
 f.facility_id = p_facility_id limit 1;

select f.*, upper(v_facility_name) as facility_name from facilities f;

end#

delimiter ;

call sp_test (1, curdate() - interval 1 month, curdate());