StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POparameters sql query inside of a stored procedure mysql
text
Body
copied!<p>I'm working with stored procedures in mysql, so I have the following procedure:</p> <pre class="lang-sql prettyprint-override"><code>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 ; </code></pre> <p>I get the following messages when I try to execute the procedure:</p> <pre><code>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 </code></pre> <p>So, what am I doing wrong?.</p> <p><strong>UPDATE 1:</strong></p> <p>The I corrected the mistake with semicolon thanks @Daniel Victoria </p> <p>But now I get the following mistake:</p> <pre><code>Error code 1267, SQL state HY000: Illegal mix of collations (latin1_spanish_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' </code></pre> <p>Exactly I get this mistake when I do </p> <pre><code>SELECT DISTINCT e.claseDocIdentidadFallecido FROM EstadisticoRD e WHERE ... AND e.codOficina=codigoEntidad; </code></pre> <p>why when I do <code>e.codOficina=codigoEntidad</code> I get this mistake, how to fixed it?.</p> <p><strong>UPDATE 2:</strong> </p> <p>To solve it, I need to put <code>COLLATE latin1_swedish_ci</code> after to the column that has the mistake. </p> <p>In this case the new query is :</p> <pre class="lang-sql prettyprint-override"><code>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; </code></pre> <p>I hope to finish this procedure the best way.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload