Agfdhyk

The issue

I have a working stored procedure (SP) that accepts 3 parameters (an id, a from date and a to date). This SP works fine in my test system where all of the values returned from t1.name in the GROUP_CONACT are rather short and there aren't many in the GROUP_CONCAT.

The issue is that when I put this in to the LIVE system, there are a few more questions to add to the GORUP_CONCAT and the t1.name values are a lot longer than expected.

From the outcome I can see when trying to debug the script, it appears that the issue is that the variable @SQL is not able to store all the information provided back from the GROUP_CONACT query.

The Code

CREATE DEFINER=`root`@`localhost` PROCEDURE `team_analyst_results`(in var1 int, in var2 DATE, in var3 DATE)

BEGIN





  SELECT

      GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(question_id = '',

                  question_id,

                  '', Pass, Null)) AS ',

                  CONCAT(''', t1.name, ''')))

  INTO @SQL FROM

      smhubdb.questions t1

          INNER JOIN

      smhubdb.check_questions t2 ON t1.id = t2.question_id

          INNER JOIN

      smhubdb.quality_checks t3 ON t2.check_id = t3.id

          INNER JOIN

      smhubdb.users t4 ON t3.check_person = t4.id

  WHERE

      t4.team_id = var1

  AND

    t3.checked_date BETWEEN var2 AND var3

  GROUP BY t3.check_person;



  SET @SQL = CONCAT('SELECT t1.id AS "'"Check ID"'", t4.name AS "'"Name"'" ,DATE_FORMAT(t1.checked_date, "'"%d-%m-%Y"'") AS "'" Check Date "'" , ', @SQL, ' , t1.notes AS "'"Notes"'", CONCAT(ROUND((SUM(CASE WHEN t2.pass = 1 THEN t3.value ELSE 0 END) / SUM(t3.value) * 100),0),"'""'") AS "'"Score"'" FROM smhubdb.quality_checks t1 LEFT JOIN smhubdb.check_questions t2 ON t1.id = t2.check_id LEFT JOIN smhubdb.questions t3 on t2.question_id = t3.id INNER JOIN smhubdb.users t4 ON t1.check_person = t4.id WHERE t4.team_id = ''', var1 ,''' AND t1.checked_date BETWEEN ''', var2 ,''' AND ''', var3 ,''' GROUP BY t1.id ORDER BY t1.id DESC');





  PREPARE stmt FROM @SQL;

  EXECUTE stmt;

END

What I tried

I tried to set @SQL as a local variable (Obviously this dropped the @) but I then get an error attempting to prepare the statement at the bottom of the code explaining that it is not expecting the local variable.

The Purpose of the code

The code is used to populate a pivot table from an unknown number of results. Meaning that I need the first GROUP_CONCAT section to build the column names on the end statement to prepare.

Expected output

The number of questions will not be know, nor will the name of the question.

table, td, th {

 border: 1px solid black;

}



table {

 border-collapse: collapse;

}

<table>

<thead>

<th>

Name

</th>

<th>

Check ID

</th>

<th>

Date

</th>

<th>

Question1

</th>

<th>

Question2

</th>

<th>

Question3

</th>

<th>

Question4

</th>

<th>

Notes

</th>

<th>

Score

</th>

</thead>

<tbody>

<tr>

<td>

Joe Bloggs

</td>

<td>

1

</td>

<td>

08/11/2018

</td>

<td>

Pass

</td>

<td>

Pass

</td>

<td>

Null

</td>

<td>

Pass

</td>

<td>

Some Notes

</td>

<td>

75%

</td>

<tr>

</tbody>

</table>

You need to increase the value of group_concat_max_len.
To do so, you can run

SET GLOBAL group_concat_max_len=..;

However, once your database is restarted, this parameter will no longer be effective. To permanently change the value of group_concat_max_len, you need to add the following line under my.cnf file:

[mysqld]

group_concat_max_len=..