retornar un valor desde un procedure

[Copia]

Hola amigos, estoy intentando que tras ejecutar un procedure este me devuelva solo un dato.
este es mi procedimiento:

Código:

CREATE DEFINER=`root`@`localhost` PROCEDURE `PA_Arribos`(xop int, xidEmpTransport int, xDireccion varchar(150), xnumVuelo int,

                                       xfechaLlegada date, xHoraLlegada time , xnumPersonas int, out xidArribo int)
BEGIN


declare xidArribo int; 

set xidArribo = (select max(idArribos)+1 from arribos);

 
 case xop

     when 1 then


          insert into arribos(idArribos, idEmpTransporte,Direccion,NumVuelo, FechaLlegada, HoraLlegada, NumPersonas)

          values(xidArribo, xidEmpTransport,xDireccion,xnumVuelo,xfechaLlegada, xHoraLlegada, xnumPersonas);
          

  end case;
            

END

quiero devolver xidArribo, porque es el id de la tabla a la que estoy insertando y necesito tener ese id para guardarlo en otra tabla, pero no lo puedo retornar.
¿Como hago para retornar ese valor?, ahora si yo trabajo con un
case xop
when 1 then
sentencia sql
return xidArribo
when 2 then

se puede devolver algun valor desde el case?? y terminar su ejecucion cuando encuentre el valor?

[gnzsoloyo]

Todo es posible... La pregunta que surge es: ¿Sobre qué lenguaje de programación estás trabajando?

Pregunto esto porque un SP parametrizado devuelve el valor en el parámetro, pero para poder recuperarlo en la aplicación el lenguaje debe poder recuperar el parámetro y leer su valor.
Este tipo de cosas son muy sencillas de hacer en .Net, pero no son tan fáciles usando mysql_query(), de PHP, por ejemplo.
Fuera de eso el SP mejor programado quedaría:

Código MySQL:

CREATE PROCEDURE `PA_Arribos`(
    IN xop INT, 
    IN xidEmpTransport INT, 
    IN xDireccion VARCHAR(150), 
    IN xnumVuelo INT,
    IN xfechaLlegada DATE, 
    IN xHoraLlegada TIME, 
    IN xnumPersonas INT, 
    OUT xidArribo INT)
BEGIN
    SET xidArribo = 0;
    IF  xop = 1 THEN
        INSERT INTO arribos(idArribos, idEmpTransporte,Direccion,NumVuelo, FechaLlegada, HoraLlegada, NumPersonas)
        VALUES(xidArribo, xidEmpTransport,xDireccion,xnumVuelo,xfechaLlegada, xHoraLlegada, xnumPersonas);
        SET xidArribo = LAST_ISERT_ID();
    END IF;
END
 

Hay algunos tips:
1) No se deben declarar variables con el mismo nombre de las variables del prototipo.
2) CASE sin un ELSE puede darte un error si la variable toma un valor no contemplado en los WHEN.
3) Si sólo vas a tomar una opción, usar un CASE carece de sentido.
4) Si lo que quieres recuperar es un valor autogenerado, para eso existen funciones específicas.

[Copia]

gracias por responder, si implemente el case fue porque tengo mas de un when, pasa que xop es la operacion que deseo realizar algo como : xop = 1 para insertar, xop = 2 para modificar, etc.. solo que lo deje ahi porque es donde estoy.

Ahora el lenguaje en el que lo implementare es en php.

y una ultima consulta, ¿es necesario ingresar los in al declarar la variable, porque sin ellos igual me lo acepta?

[gnzsoloyo]

En PHP, hasta lo que sé, sólo podrías recuperar el valor poniendo una SELECT al final, de modo que te devuelva el valor de la variable en forma de tabla de un único registro.

y una ultima consulta, ¿es necesario ingresar los in al declarar la variable, porque sin ellos igual me lo acepta?

Es una cuestión de práctica. Yo acostumbro a ser ortodoxo en la codificación para que luego me resulte más fácil leerla para modificarla o corregirla. Aparte de eso, todo parámetro cuya dirección no indicas es siempre IN.

[Copia]

no estoy seguro si se pueda devolver un dato desde un when del case, pero modifique un tanto mi codigo, y el error esta en el return porque presumo que es incorrecto.

Código:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `PA_Arribos`(xop int, xidEmpTransport int, xDireccion varchar(150), xnumVuelo int,

                                       xfechaLlegada date, xHoraLlegada time , xnumPersonas int, out xidArribo int)
BEGIN


declare xidArribo int; 

set xidArribo = (select max(idArribos)+1 from arribos);

 
 case xop



     when 1 then

          insert into arribos(idArribos, idEmpTransporte,Direccion,NumVuelo, FechaLlegada, HoraLlegada, NumPersonas)

          values(xidArribo, xidEmpTransport,xDireccion,xnumVuelo,xfechaLlegada, xHoraLlegada, xnumPersonas);
          
          return (select xidArribo);
          
    when 2 then
          select idEmpleado from cliente where idEmpleado = 1;
    
    else
          select 'Operacion no Contemplada';

  end case;

                  

END

Entonces si yo no puedo retornar los valores que quiero desde el when como hago para devolver xidArribo, ya que en otras operaciones suponen otro tipo de devoluciones... creo que me lie :S

[gnzsoloyo]

Vamos por partes:
- RETURN pertenece a un STORED FUNCION, no a un STORED PROCEDURE. Tienen una sintaxis similar, pero grandes diferencias: Una función almacenada sólo puede devolver un único dato y de un sólo tipo, el cual se debe declarar en el prototipo. Además todos los parámetros de una SF son IN.

- Si vas a devolver más de un tipo de datos, sólo puedes usar SP, pero con PHP solamente puedes leer el result de la ejecución, por lo que deberás estructurar las respuestas posibles y resolverlo en el código del script. Mi sugerencia es que uses valores numéricos para obtener las respuestas y la resolución de los mensajes la hagas en el PHP.

Algo así:

Código MySQL:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
 
CREATE PROCEDURE `PA_Arribos`(
    xop int, xidEmpTransport int, 
    xDireccion varchar(150), 
    xnumVuelo int,
    xfechaLlegada date, 
    xHoraLlegada time , 
    xnumPersonas int, 
    OUT xidArribo int)
BEGIN
    CASE xop
        WHEN 1 THEN
            INSERT INTO arribos(idArribos, idEmpTransporte,Direccion,NumVuelo, FechaLlegada, HoraLlegada, NumPersonas)
            VALUES(xidArribo, xidEmpTransport,xDireccion,xnumVuelo,xfechaLlegada, xHoraLlegada, xnumPersonas);
            SET xidArribo  = LAST_INSERT_ID();
            SELECT xidArribo;
        WHEN 2 THEN 
             SELECT idEmpleado from cliente where idEmpleado = 1;
        ELSE
             SELECT 'Operacion no Contemplada' Mensaje;
    END CASE;
END
 

Prestale atención a un par de cosas:
1) No debes usar DECLARE para declarar variables que tengan el mismo nombre que las que ya vienen en el prototipo. Es un error. Las variables del prototipo ya existen y pueden ser usadas en cualquier parte del codigo del SP.

2) Si el numero de registro es autogenerado, no tiene ninguna utilidad, y puede ser impreciso, usar MAX(x)+1 para obtener el siguiente, porque el siguiente puede no ser ese. El auto_increment se controla de otra forma y el próximo ID autogenerado mysql lo obtiene sobre la base de otra metodología. Por eso precisamente existe la función LAST_INSERT_ID(). Si esta devuelve cero, es que no se insertó nada, caso contrario devuelve el valor real del ID generado.

[Copia]

hola, gracias por responsder, uso max(idArribos) por que no es autogenerado.
Ahora, bien ahi tengo otra preguna, por ejemple xidArribos esta como OUT, entonces ese valor creo yo que no tengo que pasarlo cuando llamo a este procedimiento pero me da un error.
Si lo paso entonces creo que lo podria modificar con el set xidArribo

[gnzsoloyo]

MySQL no admite recibir menos parámetros de los que el prototipo tiene. Eso significa que no existen en MySQL los parámetros opcionales. Si le pones siete, le tienes que mandar siete.

En el caso que te ocupa, entonces, la idea sería:

Código MySQL:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
 
CREATE PROCEDURE `PA_Arribos`(
    xop INT, xidEmpTransport INT, 
    xDireccion VARCHAR(150), 
    xnumVuelo INT,
    xfechaLlegada DATE, 
    xHoraLlegada TIME , 
    xnumPersonas INT, 
    OUT xidArribo INT)
BEGIN
    CASE xop
        WHEN 1 THEN
            SELECT MAX(idArribos)+1 FROM arribos INTO xidArribo;
            INSERT INTO arribos(idArribos, idEmpTransporte,Direccion,NumVuelo, FechaLlegada, HoraLlegada, NumPersonas)
            VALUES(xidArribo, xidEmpTransport,xDireccion,xnumVuelo,xfechaLlegada, xHoraLlegada, xnumPersonas);
            SELECT xidArribo;
        WHEN 2 THEN 
             SELECT idEmpleado FROM cliente WHERE idEmpleado = 1;
        ELSE
             SELECT 'Operacion no Contemplada' Mensaje;
    END CASE;
END
 

[Copia]

muchas gracias gonzalo, se que no eres soporte tecnico pero sin abusar de tu generosidad, ni de nadie de este foro, pero tengo un error al ejecutar este procedure.

Error: 1414 SQLSTATE: 42000 (ER_SP_NOT_VAR_ARG)

Mensaje: OUT or INOUT argument %d for routine %s is not a variable or NEW pseudo-variable in BEFORE trigger

Ahora, estube buscando la solucion por internet con translate google, pero encontre alguna solucion que entendiera. Aunque hablaban de sentencias preparadas.

[gnzsoloyo]

El código en si no tiene ningún error sintáctico, al menos para cargarlo con el MySQL Workbench (ya lo probé):

Código MySQL:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
 
CREATE DEFINER=`root`@`localhost` PROCEDURE `PA_Arribos`(
    xop INT, xidEmpTransport INT, 
    xDireccion VARCHAR(150), 
    xnumVuelo INT,
    xfechaLlegada DATE, 
    xHoraLlegada TIME , 
    xnumPersonas INT, 
    OUT xidArribo INT)
BEGIN
    CASE xop
        WHEN 1 THEN
            SET xidArribo = (SELECT (MAX(idArribos)+1) FROM arribos);
            INSERT INTO arribos(idArribos, idEmpTransporte,Direccion,NumVuelo, FechaLlegada, HoraLlegada, NumPersonas)
            VALUES(xidArribo, xidEmpTransport,xDireccion,xnumVuelo,xfechaLlegada, xHoraLlegada, xnumPersonas);
            SELECT xidArribo;
        WHEN 2 THEN 
             SELECT idEmpleado FROM cliente WHERE idEmpleado = 1;
        ELSE
             SELECT 'Operacion no Contemplada' Mensaje;
    END CASE;
END$$
 

Lo único que se me ocurre es que haya algo en la forma de ejecutarlo donde haya problemas.
¿Exactamente cómo es que estás ejecutando esto? Con qué parámetros y cómo los ingresas?