title: Unable to run prepared statement because user variable is too long?
body: <h2>The issue</h2>
  
  <p>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 <code>t1.name</code> in the <code>GROUP_CONACT</code> are rather short and there aren't many in the <code>GROUP_CONCAT</code>. </p>
  
  <p>The issue is that when I put this in to the LIVE system, there are a few more questions to add to the <code>GORUP_CONCAT</code> and the <code>t1.name</code> values are a lot longer than expected. </p>
  
  <p>From the outcome I can see when trying to debug the script, it appears that the issue is that the variable <code>@SQL</code> is not able to store all the information provided back from the <code>GROUP_CONACT</code> query. </p>
  
  <h2>The Code</h2>
  
  <pre><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
  </code></pre>
  
  <h2>What I tried</h2>
  
  <p>I tried to set <code>@SQL</code> 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. </p>
  
  <h2>The Purpose of the code</h2>
  
  <p>The code is used to populate a pivot table from an unknown number of results. Meaning that I need the first <code>GROUP_CONCAT</code> section to build the column names on the end statement to prepare.  </p>
  
  <h2>Expected output</h2>
  
  <p>The number of questions will not be know, nor will the name of the question. </p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-css lang-css prettyprint-override"><code>table, td, th {
   border: 1px solid black;
  }
  
  table {
   border-collapse: collapse;
  }</code></pre>
  <pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;table&gt;
  &lt;thead&gt;
  &lt;th&gt;
  Name
  &lt;/th&gt;
  &lt;th&gt;
  Check ID
  &lt;/th&gt;
  &lt;th&gt;
  Date
  &lt;/th&gt;
  &lt;th&gt;
  Question1
  &lt;/th&gt;
  &lt;th&gt;
  Question2
  &lt;/th&gt;
  &lt;th&gt;
  Question3
  &lt;/th&gt;
  &lt;th&gt;
  Question4
  &lt;/th&gt;
  &lt;th&gt;
  Notes
  &lt;/th&gt;
  &lt;th&gt;
  Score
  &lt;/th&gt;
  &lt;/thead&gt;
  &lt;tbody&gt;
  &lt;tr&gt;
  &lt;td&gt;
  Joe Bloggs
  &lt;/td&gt;
  &lt;td&gt;
  1
  &lt;/td&gt;
  &lt;td&gt;
  08/11/2018
  &lt;/td&gt;
  &lt;td&gt;
  Pass
  &lt;/td&gt;
  &lt;td&gt;
  Pass
  &lt;/td&gt;
  &lt;td&gt;
  Null
  &lt;/td&gt;
  &lt;td&gt;
  Pass
  &lt;/td&gt;
  &lt;td&gt;
  Some Notes
  &lt;/td&gt;
  &lt;td&gt;
  75%
  &lt;/td&gt;
  &lt;tr&gt;
  &lt;/tbody&gt;
  &lt;/table&gt;</code></pre>
  </div>
  </div>
  </p>