sql not executed calling another store procedure

Question

I have trouble converting a stored procedure from Firebird to MySQL, Im new with mysql stored procedure, so please help with example :( .

So here is my current converted code:

DELIMITER $$

CREATE PROCEDURE INSERT_MRR_DETAIL (
    IN in_transactionindex INT,
    OUT monthyear INT,
    OUT day INT,
    OUT amount DOUBLE PRECISION,
    OUT acmountcumm DOUBLE PRECISION,
    OUT selisih DOUBLE PRECISION)
BEGIN
DECLARE v_customerindex INT;
DECLARE v_invoiceid INT;
DECLARE v_subscribe_date TIMESTAMP;
DECLARE v_subscribe_date_end TIMESTAMP;
DECLARE v_subscribe_sales DOUBLE PRECISION;
DECLARE v_month_days INT;
DECLARE v_month_effective_days INT;
DECLARE v_daily_amount_average DOUBLE PRECISION;
DECLARE v_lastmonthdate TIMESTAMP;
DECLARE v_month_start INT;
DECLARE v_year_start INT;
DECLARE v_month INT;
DECLARE v_year INT;
DECLARE v_month_end INT;
DECLARE v_year_end INT;
DECLARE v_amountcummulative DOUBLE PRECISION;
DECLARE v_month_amount DOUBLE PRECISION;
DECLARE v_subscribe_period_days INT;

SELECT  mrr_transaction.custid,
        mrr_transaction.invoiceid,
        mrr_transaction.datestart,
        mrr_transaction.dateend,
        mrr_transaction.amount
INTO    v_customerindex,
        v_invoiceid,
        v_subscribe_date,
        v_subscribe_date_end,
        v_subscribe_sales
FROM mrr_transaction
WHERE mrr_transaction.noindex = in_transactionindex;



SET v_subscribe_period_days = v_subscribe_date_end - v_subscribe_date + 1;

IF (v_subscribe_period_days > 0) THEN  -- Trapping Period not Zero / Null
BEGIN

  -- Define Variable Value
  SET v_month                       = extract(month from v_subscribe_date);
  SET v_year                        = extract(year from v_subscribe_date);
  SET v_month_start                 = v_month;
  SET v_year_start                  = v_year;
  SET v_month_end                   = extract(month from v_subscribe_date_end);
  SET v_year_end                    = extract(year from v_subscribe_date_end);
  SET v_daily_amount_average        = round( v_subscribe_sales / v_subscribe_period_days , 0);


  SET v_amountcummulative           = 0;


  WHILE ((v_year * 100 + v_month) <= (v_year_end * 100 + v_month_end)) DO
  BEGIN
      SET @sql = GET_EOMONTH(v_month, v_year);
      SELECT LASTDATE from @sql into v_lastmonthdate;
      SET monthyear             = v_year * 100 + v_month;
      SET v_month_days           = extract(day from v_lastmonthdate);
      SET v_month_effective_days = v_month_days;

      if ( (v_year * 100 + v_month) = (v_year_Start * 100 + v_month_start) ) then --Same with first month
            BEGIN
              v_month_effective_days = v_month_days - extract(day from v_subscribe_date) + 1;
            end
      END IF
      if ( (v_year * 100 + v_month) = (v_year_end * 100 + v_month_end) ) then    -- Same with last month
            BEGIN
              SET v_month_effective_days = extract(day from v_subscribe_date_end);
            END
      END IF

      SET v_month_amount        = v_daily_amount_average * v_month_effective_days;
      SET v_amountcummulative   = v_amountcummulative + v_month_amount;

      if ( (v_year * 100 + v_month) = (v_year_end * 100 + v_month_end) ) then    -- Same with last month
            BEGIN
              SET v_month_amount = v_month_amount + v_subscribe_sales - v_amountcummulative ;

            END
      END IF

   update mrr_Detail set isactive='F'
        where yearmonth= v_year*100 + v_month and
              transactionid = IN_TRANSACTIONINDEX;
   insert into mrr_detail(
                        custid,
                        invoiceid,
                        transactionid,
                        day,
                        month,
                        year,
                        amount,
                        yearmonth,
                        isactive)
                values(
                        V_CUSTOMERINDEX,
                        v_invoiceid,
                        IN_TRANSACTIONINDEX,
                        V_MONTH_EFFECTIVE_DAYS,
                        v_month,
                        v_year,
                        v_month_amount,
                        v_year*100 + v_month,
                        'T');

      -- Temporary Output Checking
      SET day = v_month_effective_days;
      SET amount = v_month_amount;
      SET AMOUNTCUMM = v_amountcummulative;
      SET selisih =   v_subscribe_sales - v_amountcummulative ;

      -- next month
      SET v_month = v_month+1;
      if (v_month = 13) then
        BEGIN
          SET v_month = 1;
          SET v_year = v_year + 1;
        END
      END IF

  END
  END WHILE;
END
END$$
DELIMITER ;

When I run code above in my HeidiSql, the errors come and says :

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CALL GET_EOMONTH(v_month, v_year); SELECT LASTDATE from @sql into v_lastm' at line 63 *

Any help or advice will appreciate thanks.

edited-----------------------------

I want to modify my code in to this:

SET @output_variable = 0;
  call GET_EOMONTH(v_month, v_year, @output_variable);

  SELECT LASTDATE from @output_variable into v_lastmonthdate;

But error, I want "FROM {dynamic output from GET EOMONTH}" ... How can I do that ?

@P.Salmon [This link](https://stackoverflow.com/questions/50245953/firebird-procedure-to-mysql) — Nasihun Amin Suhardiyan, May 09 '18 at 10:05
Oh wait this relates to a previous question where you created a procedure. You can only CALL a procedure you are trying to use a procedure as a function you should probably rewrite get_eomonth as a function. — P.Salmon, May 09 '18 at 10:05
So i need rewrite get_eomonth as function with another name ? @P.Salmon — Nasihun Amin Suhardiyan, May 09 '18 at 10:09
Maybe this will clarify the difference between functions and procedures https://stackoverflow.com/questions/3744209/mysql-stored-procedure-vs-function-which-would-i-use-when - up to you to decide which is most appropriate. — P.Salmon, May 09 '18 at 12:10

Ivan Cachicatari · Answer 1 · 2018-05-17T05:09:31.647

You probably are calling to a stored procedure, in MySQL the procedures doesn't return values directly (but the functions does) You may add an OUT parameter as third parameter in GET_EOMONTH procedure.

After that you can call the store procedure with that variable:

  SET @output_variable = 0;
  call GET_EOMONTH(v_month, v_year, @output_variable);
  -- do something with @output_variable
  if @output_variable > 0 then
    set v_lastmonthdate = @output_variable; 
  end if;

BUT, if you have a function the code changes to this:

  set @output_variable = GET_EOMONTH(v_month, v_year);
  -- do something with @output_variable
  if @output_variable > 0 then
    set v_lastmonthdate = @output_variable; 
  end if;

Edit:

If you have a table name in your @output_variable then you must use prepared statements:

-- use a session variable to get value from prepared statement 

set @lasmontdate_session_var = ''; -- or any value of you choose
set @sql = concat('SELECT LASTDATE from ',@output_variable,' into  @lasmontdate_session_var LIMIT 1 '); -- add limit to prevent error
prepare stmt from @sql;
execute stmt ;
deallocate prepare stmt;

-- at this point @lasmontdate_session_var contains the result of prepared query

is that example for procedure in procedure or function in procedure ? because i added `OUT ` in my GET_EOMONTH procedure — Nasihun Amin Suhardiyan, May 10 '18 at 05:33
@NasihunAminSuhardiyan I forgot to mention if `GET_EOMONTH` is a function — Ivan Cachicatari, May 10 '18 at 15:57

sql not executed calling another store procedure

1 Answers1