sql syntax error in mysql stored procedure

Question

i got this mysql procedure that has work with one machine and fails with other computer, both have mysql 5.7 and have been reading and i haven't figured out,throws a syntax error

ERROR 1064 (42000): 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 'INT)) THEN SET p_mensaje= CONCAT(p_mensaje,"El producto ", @argTemp," no se encu' at line 56

so any help will be apreciated, here is the code...

DROP  PROCEDURE IF EXISTS sp_registrar_prestamo;
CREATE PROCEDURE sistema_llaves.sp_registrar_prestamo(
in p_id_prestamo INT,   
in p_id_objeto_arg VARCHAR(1000)
)
BEGIN

DECLARE p_id_control INT(11) DEFAULT 1;
DECLARE p_mensaje VARCHAR(500) DEFAULT ''; 
DECLARE p_arreglo VARCHAR(500);
SET p_arreglo = p_id_objeto_arg;

SET @num = LENGTH(p_arreglo);
IF (@num = 0) THEN 
    SIGNAL SQLSTATE '46003'
    SET MESSAGE_TEXT='No se ingreso ningun objeto.';
END IF;

IF p_id_prestamo=0 THEN 
    IF NOT EXISTS (SELECT id FROM sistema_llaves.tprestamos) THEN
        SET @id_prest = 1;
    ELSE
        SELECT @id_prest:= max(id) FROM sistema_llaves.tprestamos;
        SET @id_prest= @id_prest + 1;
    END IF;
ELSE
    SELECT  @id_prest := MAX(id_control) FROM sistema_llaves.tprestamos WHERE id=p_id_prestamo;
    SET p_id_control = @id_prest + 1;
    SET @id_prest = p_id_prestamo;
END IF;

SET @num =(LENGTH(p_arreglo) - LENGTH(REPLACE(p_arreglo, ',', '')))+1;

IF @num = 1 THEN
    IF NOT EXISTS (SELECT id FROM sistema_llaves.tobjetos WHERE id=p_arreglo) THEN
        SET p_mensaje = CONCAT("El objeto con id ",p_arreglo, " no se encuentra en la base de datos.");
        SIGNAL SQLSTATE '46004'
        SET MESSAGE_TEXT= p_mensaje;
    ELSE
        INSERT INTO sistema_llaves.tprestamos(id,id_control,id_objeto,estado) VALUES (@id_prest,p_id_control,p_arreglo,DEFAULT);
    END IF;
ELSEIF @num > 1 THEN
    SET p_arreglo= CONCAT(p_arreglo,",");
    WHILE @num > 0 DO
        SET @argTemp=SUBSTRING(p_arreglo,1,LOCATE(",",p_arreglo)-1);

        IF NOT EXISTS(SELECT id FROM sistema_llaves.tobjetos WHERE id=CAST(@argTemp AS INT)) THEN
            SET p_mensaje= CONCAT(p_mensaje,"El producto ", @argTemp," no se encuentra en la base de datos. ");
        ELSE
            INSERT INTO sistema_llaves.tprestamos(id,id_control,id_objeto,estado) VALUES (@id_prest,p_id_control,CAST(@argTemp AS INT),DEFAULT);
            SET p_id_control= p_id_control + 1;
        END IF;
        SET @num = @num - 1;

        IF (@num >0) THEN
            SET p_arreglo := SUBSTRING(p_arreglo,LOCATE(",",p_arreglo)+1,LENGTH(p_arreglo));
        END IF;
    END WHILE;
ELSE
    SIGNAL SQLSTATE '46005'
    SET MESSAGE_TEXT='Error: Hay un problema en la cadena introducida.';
END IF;

SET @num = LENGTH(p_mensaje);
IF (@num>0) THEN
    SIGNAL SQLSTATE '46006'`enter code here`
    SET MESSAGE_TEXT= p_mensaje;
END IF;`enter code here`

END;

Barmar · Accepted Answer · 2018-11-25 10:16:05Z

1

INT is not a valid type that you can use with CAST(). To cast to an integer, use either SIGNED or UNSIGNED, with optional INTEGER (not INT) after it.

So change that line to:

IF NOT EXISTS(SELECT id FROM sistema_llaves.tobjetos WHERE id=CAST(@argTemp AS SIGNED INTEGER)) THEN

See the description of CONVERT() for all the valid conversion types.

answered Nov 25, 2018 at 10:16

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql syntax error in mysql stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related