MySql calling stored function from within a stored procedure causing error

Question

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

Schoof

I don't see the point of the function -- use the query from the function within the stored procedure. And I though that INTO needed to be before the FROM clause... – Lesbianism

well, I left out the point of the function...the function receives a username and queries a table to retrieve the account_id, then returns that account id back into a variable called account_id, which is then used for another query (the code after the "SET account_id") which I left out for ease of reading...if I comment out the line "SET account_id....." then I don't get any error. – Schoof

Answer 1

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

Schoof

what about calling the function inside BEGIN and END? – Turnstone