Error in stored procedure if parameter not in database

Databases
1

Hi I’ve got the following stored procedure

BEGIN
SELECT
    GROUP_CONCAT(
    CONCAT("MAX(IF(scadenza='", scadenza, "',importo ,'')) AS '", scadenza, "'"), "
")INTO @answers
    FROM (SELECT DISTINCT scadenza
            FROM ripartizione_rate
           WHERE preventivo = preventivoID
    ) A;
    
 SET @query :=
      CONCAT(
        'SELECT DISTINCT condomino, anagrafica,', @answers,
          'FROM ripartizione_rate
       GROUP BY condomino, anagrafica
       ORDER BY condomino, anagrafica'
      );

PREPARE statement FROM @query;
EXECUTE statement;
END

If I pass a parameter (preventivoID) that is not in the database I get the following error instead of getting NULL:

MySQL said: #1064 - 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 'NULL' at line 1

I’m not sure why this is happening?

2

If there are no members of the group, then your GROUP_CONCAT wont return anything.

What will that do to the query?

3

Hi @m_hutley thanks for your help I thought it would return null or empty clearly I’m lacking of knowledge :frowning:

4

Well, consider what happens if @answers is null.
The query becomes

       SELECT DISTINCT condomino, anagrafica, NULL
       FROM ripartizione_rate
       GROUP BY condomino, anagrafica
       ORDER BY condomino, anagrafica

Suggestion: Make @answers group concat “condomino” and “anagrafica” too. That way, it always has a value, and can never be NULL.

1 Like
5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.