SET VDATEI=(SELECT A.DATEI FROM STAFF_CONVENIOS A WHERE
   A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1);

SELECT A.DATEI, A.DIAI,A.HORAI  INTO VDATEI, VDIAI,VHORAI FROM
 STAFF_CONVENIOS A WHERE A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1;

CREATE PROCEDURE P_GETHORARIOS(
    IN `MED` BIGINT,
    IN `DATAREF` DATE,
    IN `ESPEC` BIGINT,
    IN `CONVE` BIGINT
)
BEGIN
  DECLARE BDONE,BTEMP, BOCUP INT;
  DECLARE DIA;
  DECLARE VDIA,OK TINYINT;
  DECLARE VDURACAO TINYINT;
  DECLARE VDATEI DATE;
  DECLARE VDIAI TINYINT;
  DECLARE VHORAI TIME;
  DECLARE VHORA, VHORAI,VHORAF TIME;

  DECLARE CURS CURSOR FOR  SELECT DIA,COALESCE(A.DURACAO,30) AS DURACAO, A.HINI   FROM STAFF_ESCALA A
    WHERE A.DIA=DIA;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET BDONE = 1;

  DROP TEMPORARY TABLE IF EXISTS TBLRESULTS;
  CREATE TEMPORARY TABLE IF NOT EXISTS TBLRESULTS  (
    DATA_AG DATE,
    DIA TINYINT,
    HORA TIME  );

  SET DIA=DAYOFWEEK(DATAREF);
  OPEN CURS;

  SET BDONE = 0;
  REPEAT
      SET OK=TRUE;
      FETCH CURS INTO VDIA,VDURACAO,VHORAI;

      -- SET VDATEI=(SELECT A.DATEI FROM STAFF_CONVENIOS A WHERE A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1);
      SELECT A.DATEI, A.DIAI,A.HORAI  INTO VDATEI, VDIAI,VHORAI FROM STAFF_CONVENIOS A WHERE A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1;
      SET BTEMP=IF(VDATEI IS NULL,TRUE,FALSE);
      SET OK=OK AND BTEMP ;
      IF (OK) THEN
            INSERT INTO TBLRESULTS VALUES (DATAREF,VDIA,VHORA);
      ELSE INSERT INTO TBLRESULTS VALUES (VDATEI,VDIAI,VHORAI);
    END IF;
  UNTIL BDONE END REPEAT;

  CLOSE CURS;

  SELECT A.* FROM TBLRESULTS;

END