MySql calling stored function from within a stored procedure causing error
23,626
I don't even know if it was possible what I was trying to do, which may have caused the error. But I found a work around by calling the SF as a parameter with the call to the SP and got it to do what I needed it to do.
Code is: CALL build_report_portfolio_list(get_account_id('username_here'));
Author by
Ronedog
Updated on October 30, 2020Comments
-
Ronedog over 3 years
I'm getting a 1064 error when trying to call a stored function from within a stored procedure. It only happens on the line where I try to do this:
SET account_id = get_account_id(user);
. What is the problem and how can I fix it?Account ID Stored Functions:
CREATE DEFINER=`aaron`@`%` FUNCTION `get_account_id`(user VARCHAR(255)) RETURNS int(11) BEGIN DECLARE xaccount_id INT DEFAULT 0; #Get Account ID and place into variable used when calling stored procedure that builds the tree structure for the leaf node portfolio id SELECT account_id FROM rst_sessions.session_data WHERE username = user ORDER BY update_date DESC LIMIT 1 INTO xaccount_id; RETURN xaccount_id; END
Stored Procedure that is trying to call the stored Function:
CREATE DEFINER=`aaron`@`%` PROCEDURE `build_report_portfolio_list`(user VARCHAR(255)) READS SQL DATA BEGIN DECLARE portf_id INT; DECLARE portf_name VARCHAR(255); DECLARE str_portf_parent_list VARCHAR(455); DECLARE done INT DEFAULT 0; DECLARE account_id INT; SET account_id = get_account_id(user); END
-
KD.S.T. about 6 yearswhat about calling the function inside BEGIN and END?