title: Workbench throws error in following query. Why?
body: <p>I'm sorry to post such a low quality question, but I am truly at a loss. I can't create this stored procedure because workbench keeps telling me I have syntax errors... The two errors are surrounded by ***s and are on line 6.</p>
  
  <p>When i try to save the stored procedure, the name automatically parses to <code>_SYNTAX_ERROR</code> in workbench, and the sql fails.</p>
  
  <pre><code>CREATE PROCEDURE fetchBudgetInfo(IN year YEAR, IN costCenter INT(11))
  BEGIN
      BEGIN
      SELECT gls.gl, gls.name AS gl_name, IFNULL(budgets.total, 0) as totalBudgeted,
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 1 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END), 2), 0) AS ap1,
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 1 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / ((SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year)***Error HERE (missing closing bracket)*** / 12) * 100, 2), 0 ***Error HERE (missing semicolon)***) AS ap1Ap,  
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 1 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / (SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) * 100, 2), 0) AS ap1Year,
  
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 2 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END), 2), 0) AS ap2,
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 2 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / ((SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) / 12) * 100, 2), 0) AS ap2Ap, 
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 2 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / (SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) * 100, 2), 0) AS ap2Year,
  
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 3 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END), 2), 0) AS ap3,
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 3 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / ((SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) / 12) * 100, 2), 0) AS ap3Ap, 
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 3 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / (SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) * 100, 2), 0) AS ap3Year,
  
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 4 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END), 2), 0) AS ap4,
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 4 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / ((SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) / 12) * 100, 2), 0) AS ap4Ap, 
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 4 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / (SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) * 100, 2), 0) AS ap4Year,
  
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 5 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END), 2), 0) AS ap5,
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 5 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / ((SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) / 12) * 100, 2), 0) AS ap5Ap, 
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 5 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / (SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) * 100, 2), 0) AS ap5Year,
  
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 6 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END), 2), 0) AS ap6,
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 6 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / ((SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) / 12) * 100, 2), 0) AS ap6Ap, 
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 6 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / (SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) * 100, 2), 0) AS ap6Year,
  
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 7 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END), 2), 0) AS ap7,
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 7 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / ((SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) / 12) * 100, 2), 0) AS ap7Ap, 
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 7 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / (SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) * 100, 2), 0) AS ap7Year,
  
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 8 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END), 2), 0) AS ap8,
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 8 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / ((SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) / 12) * 100, 2), 0) AS ap8Ap, 
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 8 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / (SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) * 100, 2), 0) AS ap8Year,
  
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 9 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END), 2), 0) AS ap9,
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 9 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / ((SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) / 12) * 100, 2), 0) AS ap9Ap, 
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 9 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / (SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) * 100, 2), 0) AS ap9Year,
  
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 10 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END), 2), 0) AS ap10,
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 10 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / ((SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) / 12) * 100, 2), 0) AS ap10Ap, 
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 10 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / (SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) * 100, 2), 0) AS ap10Year,
  
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 11 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END), 2), 0) AS ap11,
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 11 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / ((SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) / 12) * 100, 2), 0) AS ap11Ap, 
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 11 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / (SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) * 100, 2), 0) AS ap11Year,
  
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 12 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END), 2), 0) AS ap12,
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 12 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / ((SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) / 12) * 100, 2), 0) AS ap12Ap, 
          IFNULL(ROUND(SUM(CASE WHEN requests.ap = (SELECT id FROM ap WHERE ap.ap = 12 AND YEAR(ap.start_date) = year) THEN totalCost ELSE 0 END) / (SELECT total FROM budgets WHERE costCenter = costCenter AND gl = gls.id AND YEAR(budgets.year) = year) * 100, 2), 0) AS ap12Year,
  
          IFNULL(ROUND(SUM(totalCost), 2), 0) AS totalCost,
          IFNULL(ROUND(SUM(totalCost) / (SELECT total FROM budgets WHERE budgets.costCenter = costCenter AND budgets.gl = gls.id) * 100, 2), 0) AS totalPercent
  
      FROM gls
          LEFT JOIN requests ON requests.glid = gls.id AND requests.costCenter = costCenter AND requests.status = 'approved' AND (SELECT YEAR(ap.start_date) FROM ap WHERE ap.id = requests.ap) = year
          LEFT JOIN budgets ON budgets.gl = gls.id AND budgets.costCenter = costCenter
  
      GROUP BY gls.id;
  END
  </code></pre>
  
  <p>EDIT</p>
  
  <p>This also fails....</p>
  
  <pre><code>CREATE PROCEDURE fetchBudgetInfo(IN year YEAR, IN costCenter INT(11))
  BEGIN
      SELECT gls.id, gls.gl, gls.name AS gl_name, budgets.total as totalBudgeted
  
      GROUP BY gls.id;
  END
  </code></pre>