title: user defined variable increment in mysql pocedure
body: <p>i am trying to use a user defined variable in mysql procedure.Which is being incremented as well decremented according to the value.two select statement are joined in it and the variable is being used in both statement.Also a fuction will be placed instead of static value in assigning variable. can anyone please help where i made mistake? i am unable to create procedure it due to this error.
  Thank you.</p>
  
  <p><strong>error:</strong>
  <em>#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 'END' at line 59</em></p>
  
  <pre><code>DELIMITER $$
  CREATE DEFINER=`local`@`localhost` PROCEDURE `sp_select_customer_account_lesuire`(IN `date_from` VARCHAR(10), IN `date_to` VARCHAR(10), IN `account_id` INT(11) UNSIGNED, IN `company_name` INT(11) UNSIGNED, IN `start_row` INT(11) UNSIGNED, IN `end_row` INT(11) UNSIGNED)
      NO SQL
  BEGIN
  SET @runningBalance := -199999; /*need a function for dynamic value*/
  SELECT
  c.`customer_account_id` as 'id',
  c.`received` as 'date',
  c.`description` as 'desc',
  '' as 'sales_type',
  NULL as 'sales_us',
  NULL as 'sales_rate',
  NULL as 'sales_yen',
  CONCAT('$ ',FORMAT(c.`usd_amount`,0)) as 'us',
  c.`rate` as 'rate',
  CONCAT('&amp;yen; ',FORMAT(c.`yen_amount`,0)) as 'yen',
  (@runningBalance := @runningBalance-c.`yen_amount`) as 'balance'
  
  FROM `customer_account` c
  
  LEFT JOIN `customer_detail` cd ON
  cd.`customer_detail_id` = c.`customer_detail_id`
  
  WHERE
  (
      (account_id='' OR c.customer_account_id=account_id)
      AND
      (cd.customer_detail_id=company_name)
      AND
      (c.received BETWEEN (case WHEN (date_from="" OR date_from="0000-00-00") THEN '1990-01-01' ELSE date_from END) AND (case WHEN (date_to="" OR date_to="0000-00-00") THEN CURRENT_DATE ELSE date_from END))
  )
  GROUP BY c.customer_account_id
  UNION
  SELECT
  cs.`invoice_id`,
  cs.`sales_date`,
  CONCAT('INV#',cs.`invoice_no`),
  (case WHEN cs.`sales_type`='1' THEN 'FOB' WHEN cs.`sales_type`='2' THEN 'C&amp;F' WHEN cs.`sales_type`='3' THEN 'CIF' WHEN cs.`sales_type`='4' THEN 'Dealer' WHEN cs.`sales_type`='5' THEN 'Auction'END),
  case WHEN cs.`currency`='usd' THEN CONCAT('$ ',FORMAT((cs.`grand_total`/cs.`exchange_rate`),0)) ELSE NULL END,
  case WHEN cs.`exchange_rate` &gt; 1 THEN cs.`exchange_rate` ELSE NULL END,
  CONCAT('&amp;yen; ',FORMAT(cs.`grand_total`,0)),
  NULL,
  NULL,
  NULL,
  (@runningBalance := @runningBalance+cs.`grand_total`)
  
  FROM `car_sales` cs
  
  LEFT JOIN `customer_detail` cd ON
  cd.`customer_detail_id` = cs.`buyer_name`
  
  WHERE 
  (
      (cd.customer_detail_id=company_name)
      AND
      (cs.sales_date BETWEEN (case WHEN (date_from="" OR date_from="0000-00-00") THEN '1990-01-01' ELSE date_from END) AND (case WHEN (date_to="" OR date_to="0000-00-00") THEN CURRENT_DATE ELSE date_from END))
  )
  ORDER BY 2 ASC
  LIMIT start_row, end_row
  END$$
  DELIMITER ;
  </code></pre>