Error in stored procedure if parameter not in database

Hi I’ve got the following stored procedure

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

PREPARE statement FROM @query;
EXECUTE statement;

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?

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

What will that do to the query?

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

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

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