0

I'm using phpMyAdmin with MySQL and when I try to execute query below I got error #1064 for the declaration of @sql_query

DELIMITER  $$
DROP PROCEDURE IF EXISTS  usuarioExiste  $$
CREATE PROCEDURE  usuarioExiste (IN id_usuario_busca INT)

BEGIN

DECLARE apeli_nombre VARCHAR(50);
DECLARE existe INTEGER;
DECLARE file_name VARCHAR(50);

SET existe = (SELECT COUNT(id_usuario) FROM usuario WHERE id_usuario = id_usuario_busca);
SET apeli_nombre = ( SELECT CONCAT(apellido, "_" , nombre) FROM usuario WHERE id_usuario = 1);
SET file_name = CONCAT(apeli_nombre, '.txt');
SET @sql_query = Concat("SELECT SUM(compra.id_producto)INTO OUTFILE /", file_name," FROM ls_shop.compra  WHERE id_usuario_busca = (SELECT id_usuario FROM  compra )" );
    IF existe > 0 THEN
        BEGIN   
          UPDATE usuario
          SET nombre=Concat(UPPER(LEFT(nombre,1)),LOWER(SUBSTRING(nombre,2))),
          apellido =Concat(UPPER(LEFT(apellido,1)),LOWER(SUBSTRING(apellido,2)))
          WHERE id_usuario = id_usuario_busca; 
          
          PREPARE stmt1 FROM @sql_query;
          EXECUTE stmt1;
          DEALLOCATE PREPARE stmt1;

          SELECT apellido, nombre 
          FROM usuario
          WHERE id_usuario = id_usuario_busca; 
        END;
    END IF;
END $$
DELIMITER ;

Error from this part:

SET @sql_query = Concat("SELECT SUM(compra.id_producto)INTO OUTFILE /", file_name," FROM ls_shop.compra  WHERE id_usuario_busca = (SELECT id_usuario FROM  compra )" );

I would like create File like /name_surname.txt with selected id

Milirium
  • 31
  • 1
  • 4
  • Looks like the only bit that shouldn't be enclosed in quotes is file_name. Also you can't use declared variables when using dynamic sql use @ variable instead.. – P.Salmon Jan 15 '22 at 10:52
  • Edited with your change, but I got error to declare variable with @ – Milirium Jan 15 '22 at 11:02
  • You don't declare user defined variables see https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql – P.Salmon Jan 15 '22 at 11:03
  • which variable is not declared ? – Milirium Jan 15 '22 at 11:29
  • 1) `@sql_query` and `sql_query` are two different variables. 2) `@sql_query` does not need to be declared. *Error from this part:* - no, from *`DECLARE @sql_query ..`*. – Akina Jan 15 '22 at 11:34
  • I got this error with updates : The following query has failed: "SET @p0='1'; CALL `usuarioExiste`(@p0); " MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '/Polo_Toto.txt FROM ls_shop.compra WHERE id_usuario_busca = (SELECT id_usuar...' at line 1 – Milirium Jan 15 '22 at 18:55

0 Answers0