0

I'm working with stored procedures in mysql, so I have the following procedure:

DELIMITER @@
DROP PROCEDURE IF EXISTS generarEstadisticoRD @@
CREATE PROCEDURE generarEstadisticoRD ( mesInicial INT,anualInicial INT, mesFinal INT,anualFinal INT, codigoEntidad CHAR(3),mes INT )

BEGIN
    DECLARE controlador INT;
    DECLARE tipoDocumento CHAR(2);

    DECLARE cursorDocumentos CURSOR FOR SELECT DISTINCT e.claseDocIdentidadFallecido 
            FROM EstadisticoRD e  WHERE e.anual>=anualInicial AND e.anual<=anualFinal  
            AND  e.mes >=mesInicial AND e.mes<=mesFinal AND e.codOficina=codigoEntidad;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET controlador = 1;

    DROP TEMPORARY TABLE IF EXISTS estadistico;
    CREATE TEMPORARY TABLE IF NOT EXISTS 
    estadistico( TIPO CHAR(2), MES INT );

    OPEN cursorDocumentos;

    cursorLoop : LOOP
        FETCH cursorDocumentos INTO tipoDocumento;
        IF( controlador=1 ) THEN
            LEAVE cursorLoop;
        END IF 

        /**
          *Lógica
          */
        INSERT INTO estadistico(`TIPO`,`MES`) 
            SELECT DISTINCT 
                c.descripcion, 
                IFNULL( (SELECT e.numRegistrosReportados FROM estadisticoRD e WHERE e.codOficina=codigoEntidad
                    AND e.claseDocIdentidadFallecido=tipoDocumento AND e.mes=mes ), 0)
            FROM estadisticoRD e, claseDoc c WHERE e.codOficina=codigoEntidad AND e.claseDocIdentidadFallecido=tipoDocumento
            AND c.claseDoc = e.claseDocIdentidadFallecido;

    END LOOP cursorLoop;
    CLOSE cursorDocumentos;
    SELECT * FROM estadistico;
END @@ 
DELIMITER ;

I get the following messages when I try to execute the procedure:

Executed successfully in 0,001 s, 0 rows affected.
Line 2, column 1

Error code 1064, SQL state 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 'INSERT INTO estadistico(`TIPO`,`MES`) 
        SELECT DISTINCT c.descripcion, 
 ' at line 24
Line 3, column 1

So, what am I doing wrong?.

UPDATE 1:

The I corrected the mistake with semicolon thanks @Daniel Victoria

But now I get the following mistake:

Error code 1267, SQL state HY000: Illegal mix of collations (latin1_spanish_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

Exactly I get this mistake when I do

SELECT DISTINCT e.claseDocIdentidadFallecido 
            FROM EstadisticoRD e  WHERE ...  AND e.codOficina=codigoEntidad;

why when I do e.codOficina=codigoEntidad I get this mistake, how to fixed it?.

UPDATE 2:

To solve it, I need to put COLLATE latin1_swedish_ci after to the column that has the mistake.

In this case the new query is :

SELECT DISTINCT * 
            FROM estadisticoRD e  WHERE e.anual>=anualInicial AND e.anual<=anualFinal  
            AND  e.mes >=mesInicial AND e.mes<=mesFinal AND e.codOficina  = codigoEntidad COLLATE latin1_swedish_ci;

I hope to finish this procedure the best way.

1 Answer 1

1

Your are missing a semicolon (;) after the "END IF"

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

1 Comment

Vitoria, now I have the following error when I try to call the procedure Error code 1267, SQL state HY000: Illegal mix of collations (latin1_spanish_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' What can it be?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.