一千萬個為什麽

搜索

在MySQL中查詢中選擇存儲過程值

我發現很多線程接近這個,但我沒有回答我的問題。

我正在編寫一個報告,需要使用我聲明為SELECT字段之一的變量。

這是商店程序代碼:     DROP PROCEDURE如果存在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”放在我的查詢結果中而不是它的值。

有沒有辦法強制它在那裏輸出值?

最佳答案

我稍微清理了你的代碼 - 以下應該提供一個很好的起點,你可以繼續...

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());

轉載註明原文: 在MySQL中查詢中選擇存儲過程值