Differences when executing stored procedure in local and server

Navigation
1

I have the following stored procedure, which I run locally in Workbench and do what I need; however, I have the same database stored on a test server, and that's where I get the following message when I run it:

  

MySQL returned an empty result set (i.e. zero rows).

My database is for a dental office, my appointment table is as follows: 

CREATE TABLE CitaMedica
(
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Fecha_Registro DATETIME DEFAULT CURRENT_TIMESTAMP,
    Fecha_Programada DATE NOT NULL,
    Hora_Programada TIME NOT NULL,
    Motivo NVARCHAR(100) NOT NULL,
    Estado BOOLEAN NULL DEFAULT TRUE,
    Paciente_ID INT UNSIGNED NOT NULL,
    Servicio_ID INT UNSIGNED NOT NULL,
    PRIMARY KEY(ID)  
)
ENGINE = InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000;

The Stored Procedure is as follows: 

DELIMITER $$
CREATE PROCEDURE 'cancelarCita'
(
IN param_id INT UNSIGNED
)
BEGIN
START TRANSACTION;
    IF EXISTS(SELECT Estado = TRUE FROM CitaMedica WHERE ID = param_id)
    THEN
        DELETE FROM CitaMedica WHERE ID = param_id;
    ELSE 
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: No se encontró cita para el paciente.';
COMMIT;
END IF;
END$$;
DELIMITER ;

Will it be any difference in the configuration?

    
asked by Daniel Snv 28.03.2017 в 02:09
source

1 answer

1

Change your Stored Procedure to: 

DELIMITER $$
CREATE PROCEDURE 'cancelarCita'
(
IN param_id INT UNSIGNED
)
BEGIN
START TRANSACTION;
    IF (SELECT count(*) FROM CitaMedica WHERE ID = param_id and Estado = true)>0
    THEN
      BEGIN
        DELETE FROM CitaMedica WHERE ID = param_id;
      END;
    ELSE 
      BEGIN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 
        'Error: No se encontró  cita para el paciente.';
      COMMIT;
     END;
   END IF;
   END$$;
DELIMITER ;
    
answered by 28.03.2017 в 04:48
Error generating APK in Android Studio How can I export 2 HTML tables to Excel