CODEWITHSUNDEEP
mysqlstored-procedures
Abdul Ali
Abdul Ali

Reputation: 1937

declaring variable inside mysql stored procedure

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 :

following is the syntax of the stored procedure:

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;

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 ..

any help is appreciated..

Upvotes: 0

Views: 2609

Answers (1)

piotrgajow
piotrgajow

Reputation: 2950

All declare statements should be at the top of the stored procedure body. Moving DECLARE EXIT HANDLER before the SET statement should fix the problem.

Upvotes: 1

Related Questions