how can I fix this sp in phpmyadmin?

Question

I am trying to write a store procedure in phpmyadmin but I get this error, also I did it in navicat but I get the same error, can someone help me to fix it?, help really appreciated thanks

#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 ' IN comienzo int, IN termino int ) BEGIN SELECT usuario.nombre, LEF' at line 2

this is my store procedure:

    CREATE PROCEDURE store(
  IN actualUser varchar(20)
)

SET @comienzo2 = comienzo;
SET @termino2 = termino;
SET @actualUser2 = actualUser;

BEGIN
SELECT
usuario.nombre,
LEFT(titulo, 20) as titulo,
LEFT(mensaje, 20) as mensaje,
correo.fecha,
correo.id_correo,
correo_has_usuario.correo_id_correo,
correo_has_usuario.destinatario_id_usuario,
correo_has_usuario.eliminado,
correo_has_usuario.leido
FROM
correo_has_usuario 
Inner Join correo ON correo_has_usuario.correo_id_correo = 
correo.id_correo
Inner Join usuario ON usuario.id_usuario = 
correo.usuario_id_usuario
WHERE
correo_has_usuario.destinatario_id_usuario = actualUser AND
correo_has_usuario.eliminado =  0 ORDER BY correo.fecha DESC
LIMIT comienzo,termino;
END$$

Hello thanks again for answer I get this error message #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 'SET @termino2 = termino; SET @actualUser2 = actualUser; BEGIN PREPARE stmt ' at line 8

Answer 1

You've missed the data length off of the VARCHAR on line 2

IN actualUser varchar(20) for example

Your limit statement is also incorrect and will throw out an error. Limits must be numeric or a local variable when used in a stored procedure. See

http://dev.mysql.com/doc/refman/5.5/en/select.html

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.

To do this you'd have to set up your arguments as variables like

SET @comienzo2 = comienzo;
SET @termino2 = termino;
SET @actualUser2 = actualUser;

Then rather than simply run your select statement you must prepare it then execute it.

PREPARE stmt FROM 'SELECT ... LIMIT ?, ?';
EXECUTE stmt USING @comienzo2, @termino2;
DEALLOCATE PREPARE stmt;

This will also be the case for your where statement.

Example:

DELIMITER $$
CREATE PROCEDURE store(
  IN actualUser varchar(20),
  IN comienzo int,
  IN termino int
)

SET @comienzo2 := comienzo;
SET @termino2 := termino;
SET @actualUser2 := actualUser;

BEGIN
PREPARE stmt FROM 'SELECT
    usuario.nombre,
    LEFT(titulo, 20) as titulo,
    LEFT(mensaje, 20) as mensaje,
    correo.fecha,
    correo.id_correo,
    correo_has_usuario.correo_id_correo,
    correo_has_usuario.destinatario_id_usuario,
    correo_has_usuario.eliminado,
    correo_has_usuario.leido
    FROM
    correo_has_usuario 
    Inner Join correo ON correo_has_usuario.correo_id_correo = 
    correo.id_correo
    Inner Join usuario ON usuario.id_usuario = 
    correo.usuario_id_usuario
    WHERE
    correo_has_usuario.destinatario_id_usuario = ? AND
    correo_has_usuario.eliminado =  0 ORDER BY correo.fecha DESC
    LIMIT ?,?;'
EXECUTE stmt USING @actualUser2, @comienzo2, @termino2;
DEALLOCATE PREPARE stmt;
END;
$$