title: declaring variable inside mysql stored procedure
body: <p>we are trying to declare a variable inside mysql stored procedure that has transaction implemented in it. but it seems to be giving a syntax error :</p>
  
  <p>following is the syntax of the stored procedure:</p>
  
  <pre><code>CREATE PROCEDURE `sp_MarkAppointmentRefferal`(
    p_AppId bigint,
    p_NewLocation bigint,
    p_userId bigint,
    p_ReferralReason varchar(500),
    p_NewLocationName varchar(100)
  )
  begin
  
  
  declare v_OldLocation int default 0;
  set v_OldLocation = (select LocationId FROM appointments where iAppID = p_AppId limit 1 );
  
   DECLARE EXIT HANDLER FOR SQLEXCEPTION 
      BEGIN
          ROLLBACK;
          select -1;
      END;  
  
  
  START TRANSACTION;
  
  
  
  
      update table
  set is_referred = 1,
   referred_timestamp = now(),
    referral_reason = p_ReferralReason
   where iAppID = p_AppId
   limit 1;
  
    -- create a new appointment for the new referred location..
  
    insert into appointments
    (vAppName, vAppType, dAppDate, vCell, iPatID, iAppStatus, iuserid, iActive, 
    dInsertDate, iHSID, daily_ticket_no, LocationId, visit_id, encounter_id, ReferredFrom,ReferredOPDName, opd_name )
  
      select vAppName, vAppType, now(), vCell, iPatID, iAppStatus, p_userId, 
      1, now(), iHSID, fn_GenerateNextAppointmentTicket(now(),p_NewLocation) , p_NewLocation, visit_id, encounter_id+1, 
      (select LocationId FROM appointments where iAppID = p_AppId limit 1),
      (select OPD_Name FROM appointments where iAppID = p_AppId limit 1), p_NewLocationName
      FROM appointments
      where iAppID = p_AppId limit 1;
  
      select LAST_INSERT_ID();
  
  COMMIT;
  
  
  
  end;
  </code></pre>
  
  <p>the syntax checker is saying that declare command is not valid here.
  have also tried to place this inside the transaction clause and similar error shows up .. </p>
  
  <p>any help is appreciated.. </p>