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