MySQL export into Outfile issue

Question

I created a Stored procedure with a cursor in order to get the info from a few tables into a temp table and then export the result into multiple files, one for each Client ID, the cursor and the loop get all the info right but I'm having syntax issues when creating each file

BEGIN
    DECLARE id_cli INT;
  DECLARE cur_id_cli CURSOR FOR SELECT id FROM cliente ORDER BY id;

  OPEN cur_id_cli;

  read_loop: LOOP
    FETCH cur_id_cli INTO id_cli;


DROP TEMPORARY TABLE IF EXISTS tmp_reporte_enviadas_sucursal_mensual;
  CREATE TEMPORARY TABLE tmp_reporte_enviadas_sucursal_mensual

  SELECT  *  FROM
  ((SELECT 'Promo_id', 'Mensaje', 'Sucursal_id', 'Direccion') UNION ALL (SELECT
      p.id,
          p.mensaje,
          s.id,
          s.direccion
  FROM
      Usuario_Promo AS up, Promo_Sucursal AS ps, Cliente_Sucursal AS cs, Cliente AS c, Promo AS p, Sucursal AS s
  WHERE
      p.id = ps.Promo_id
          AND up.promo_id = ps.id
          AND up.recibido = 1
          AND ps.Cliente_Sucursal_id = cs.id
          AND cs.cliente_id = id_cli
          AND DATE(up.fecha_recibido) BETWEEN (NOW() - INTERVAL 30 DAY) AND NOW()
          AND s.id = cs.sucursal_id
  ORDER BY p.id)) AS tmp_reporte_enviadas_sucursal_mensual;

SET @idCli = CAST(id_cli AS CHAR);
SET @fullOutputPath = CONCAT('D:/Octagon/Apps/Flyermob/Clientes/Reportes_Sql/reporte_enviadas_sucursal_mensual_',@idCli,'.csv');

SELECT * FROM
INTO OUTFILE @fullOutputPath 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\"'
FROM tmp_reporte_enviadas_sucursal_mensual;

SELECT * FROM tmp_reporte_enviadas_sucursal_mensual;

  END LOOP;

  CLOSE cur_id_cli;
END

here's where I'm having the issue:

SELECT * FROM
    INTO OUTFILE @fullOutputPath 
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '\"'
    FROM tmp_reporte_enviadas_sucursal_mensual;

Whenever I try to save, it says that there's a syntax problem near @fullOutputPath if I remove this section, the procedure run perfectly and I get all the results for each Client Id.

Just to get it out of the way id_cli is a primary key, so there's no chance of repeating and I have full permissions on @fullOutputPath.

Answer 1

I managed to make it work with

SET @idCli = CAST(id_cli AS CHAR);
SET @fullOutputPath = CONCAT('flyermob/cliente/reporte_enviadas_sucursal_mensual_cliente_',@idCli,'.csv');

set @q1 := concat("SELECT * INTO OUTFILE '",@fullOutputPath,
"' FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\"'
FROM tmp_reporte_enviadas_sucursal_mensual");

prepare s1 from @q1;
execute s1;
deallocate prepare s1;

It creates the file in the MySql default folder