Mysql error 1644

Every time i execute mysql stored procedure i getting SQL Error [1644] [45000]: Variable ‘MESSAGE_TEXT’ can’t be set to the value of ‘NULL’

To understand what exact i tried all the option but no luck

Issue :

1. Execute stored procedure first time — Success
2. Execute stored procedure second time — Error

Temp Workaround :

1. Execute stored procedure first time — Success
2. Put same blank space in non code area and save
3. Execute stored procedure — Success

I am not getting what exactly the issue and how to resolve it, if anyone can help will be appriciated.

CREATE PROCEDURE  OrderStatusInsert(IN  _json JSON)
BEGIN
    
    DECLARE   _tempDocNo varchar(50);
    DECLARE  _Flag VARCHAR(50);
    DECLARE _AWBNo  VARCHAR(50);
    DECLARE  _FirstActionDate VARCHAR(50);
     DECLARE _DriverId VARCHAR(25);
     DECLARE _RunsheetNo varchar(100);
     DECLARE _InsertedBy INT;
    DECLARE  _ShelveId VARCHAR(45); 
    DECLARE  _ShelveCode VARCHAR(45); 
    DECLARE  _LocationCode  VARCHAR(20); 
    DECLARE  _VehicleNo  VARCHAR(25); 
    DECLARE  _DriverDetail VARCHAR(200); 
    DECLARE  _LineCount INT default 0;
    DECLARE  _ErrorMessage VARCHAR(500) ; 
     DECLARE _UniqueId VARCHAR(100) DEFAULT  DATE_FORMAT(NOW(),'%Y%m%d%H%i%s');
     DECLARE EXIT HANDLER FOR SQLEXCEPTION
     BEGIN    
        GET  DIAGNOSTICS  CONDITION  1 _ErrorMessage =  Message_Text;      
        ROLLBACK; 
         SET SQL_SAFE_UPDATES = 0;
         INSERT INTO  SqlExceptionData(SPName,ErrorMessage,Parameters,InsertedOn,InsertedBy)
         VALUES  ('OrderStatusInsert',_ErrorMessage,_json,NOW(),'SYSTEM');
          
        SIGNAL SQLSTATE '45000'  
        SET MESSAGE_TEXT =  _ErrorMessage; 
    END;    
     
     SET SQL_SAFE_UPDATES = 0;
    DROP  TEMPORARY TABLE IF EXISTS tempDRSJson;
     CREATE TEMPORARY TABLE tempDRSJson 
     SELECT    JSON_UNQUOTE(JSON_EXTRACT(_json,'$.Flag'))  as Flag
         ,JSON_UNQUOTE(JSON_EXTRACT(_json,'$.RunsheetNo'))as  RunsheetNo
         ,JSON_UNQUOTE(JSON_EXTRACT(_json,'$.LocationCode'))  as LocationCode
         ,JSON_UNQUOTE(JSON_EXTRACT(_json,'$.VehicleNo'))  as VehicleNo
         ,IFNULL(JSON_UNQUOTE(JSON_EXTRACT(_json,'$.DriverId')),0)   as DriverId
         ,JSON_UNQUOTE(JSON_EXTRACT(_json,'$.AWBNo'))   as AWBNo
         ,IFNULL(JSON_UNQUOTE(JSON_EXTRACT(_json,'$.InsertedBy')),0)   as InsertedBy
    ;
    SELECT Flag,  RunsheetNo, InsertedBy, DriverId INTO  _Flag, _RunsheetNo, _InsertedBy,  _DriverId FROM tempDRSJson ;
       
     DROP TEMPORARY TABLE IF EXISTS tempDRS;
     CREATE TEMPORARY TABLE tempDRS (
     SRNO int(11) NOT NULL AUTO_INCREMENT 
     ,Id int(11)
    ,RunsheetNo   varchar(50) DEFAULT NULL 
    ,AWBNo  varchar(50) DEFAULT NULL 
    ,City  varchar(70) DEFAULT NULL 
    ,DriverId  varchar(50) DEFAULT NULL 
     ,DriverName varchar(60) DEFAULT NULL 
     ,Status varchar(50) DEFAULT NULL 
     ,DriverPhoneNo varchar(50) DEFAULT NULL  
    ,PaymentMethod varchar(50) DEFAULT  NULL 
    ,CollectableAmount INT default  0
    ,FacilityAddress varchar(100)  DEFAULT NULL 
    ,PhoneNumber1  varchar(50) DEFAULT NULL 
    ,Consignee  varchar(50) DEFAULT NULL 
     ,ConsigneeAddress1 varchar(250) DEFAULT  NULL 
    ,Latitude varchar(50) DEFAULT  '0'
    ,Longitude varchar(50) DEFAULT  '0'
    ,InsertedOn varchar(50) DEFAULT  NULL 
    ,InsertedBy varchar(50)  DEFAULT NULL 
    ,VehicleNo varchar(25)  DEFAULT NULL 
    ,LocationCode  varchar(25) DEFAULT NULL 
    ,PRIMARY  KEY (SRNO),
    KEY IDX_RunsheetNo  (RunsheetNo) ); 
 
    IF  (UPPER(_Flag)='INSERTLINE') THEN
     BEGIN   
          
            IF NOT  EXISTS(SELECT DISTINCT 1 FROM  LocationMaster  LM INNER JOIN  tempDRSJson TR ON  TR.LocationCode=LM.LocationCode LIMIT 1   ) THEN 
            BEGIN 
                 SET _ErrorMessage = CONCAT('Invalid  location') ; 
                SIGNAL  SQLSTATE '45000' SET MESSAGE_TEXT =  _ErrorMessage; 
            END; 
             ELSE IF NOT EXISTS(SELECT DISTINCT  1 FROM ShipmentData SD INNER JOIN  tempDRSJson TR ON TR.AWBNo=SD.AWBNo  LIMIT 1  ) THEN 
            BEGIN 
                 SET _ErrorMessage =  'Invalid  box'  ; 
                SIGNAL SQLSTATE  '45000' 
                SET  MESSAGE_TEXT = _ErrorMessage; 
             END; 
            ELSE IF(SELECT  DISTINCT 1 FROM DRSLine DL INNER JOIN  tempDRSJson TR ON TR.AWBNo=DL.AWBNo AND  DL.DriverId != IFNULL(TR.DriverId,0))  THEN
            BEGIN  
                 SELECT CONCAT('Box: ',  TR.AWBNo, ' is  already scanned by driver : ', DM.Name ,  ' in RunsheetNo: ', DL.RunsheetNo)
                 INTO  _ErrorMessage 
                 FROM DRSLine DL INNER JOIN  UserMaster DM ON DL.DriverId = DM.Id
                 INNER JOIN tempDRSJson TR ON  TR.AWBNo=DL.AWBNo  
                AND  DL.DriverId != TR.DriverId;  
                 SIGNAL SQLSTATE '45000'      SET  MESSAGE_TEXT = _ErrorMessage; 
             END; 
            ELSE IF  EXISTS(SELECT DISTINCT 1 FROM DRSLine DL  INNER JOIN tempDRSJson TR ON  TR.AWBNo=DL.AWBNo ) THEN 
             BEGIN  
                SELECT  CONCAT('Box: ',  AWBNo, ' is already  scanned') INTO _ErrorMessage FROM  tempDRSJson; 
                SIGNAL  SQLSTATE '45000'     SET MESSAGE_TEXT =  _ErrorMessage;
            END; 
             ELSE IF EXISTS(SELECT 1 FROM  RunsheetDetail DRS INNER JOIN  tempDRSJson TR ON TR.AWBNo=DRS.AWBNo  LIMIT 1) THEN
            BEGIN  
                 SELECT DISTINCT concat('Box  already scanned in another Runsheet: ',  RunsheetNo) into _ErrorMessage  FROM  RunsheetDetail WHERE AWBNo = _AWBNo ; 
                 SIGNAL SQLSTATE '45000'  SET MESSAGE_TEXT = _ErrorMessage; 
             END;  
            ELSE IF   EXISTS(SELECT 1 FROM ReScheduleDetails  RD INNER JOIN tempDRSJson TR ON  TR.AWBNo=RD.AWBNo LIMIT 1) THEN
             BEGIN  
                 SIGNAL  SQLSTATE '45000' SET MESSAGE_TEXT ='AWB  has Reschedule without date.' ;
             END;   
            END IF;  
             END IF;  
            END IF; 
             END IF; 
            END IF; 
             END IF; 
                SELECT  CONCAT('Runsheet created for driver: '
                 , ' - ',COALESCE(cast(  UM.UserId as char))) Driver
                 INTO _ErrorMessage 
                 FROM  DRSHeader  DH  
                 INNER JOIN  UserMaster UM ON DH.DriverId  = UM.Id AND UM.RoleId=1   
                 INNER JOIN tempDRSJson TR ON  TR.RunsheetNo=DH.RunsheetNo AND 
                 NOT  DH.DriverId=IFNULL(TR.DriverId,0) LIMIT  1 ;  
              
                IF  (IFNULL(_ErrorMessage,'')!='' OR  _ErrorMessage!='NULL') THEN
                 BEGIN
                     SIGNAL  SQLSTATE '45000' SET MESSAGE_TEXT =  _ErrorMessage;  
                END;
                 ELSE
                BEGIN     
                    
                     INSERT INTO DRSLine(RunsheetNo,  ShelvingId, DriverId, AWBNo, Status,  InsertedBy, InsertedOn, LocationCode) 
                     SELECT RunsheetNo, 0,   DriverId,  AWBNo, 'Shipped',   InsertedBy, NOW(), LocationCode
                     FROM tempDRSJson ;
                     
                    UPDATE   DRSHeader  DH  INNER JOIN tempDRSJson TR  ON DH.RunsheetNo = TR.RunsheetNo 
                     SET DH.DriverId =   TR.DriverId, DH.VehicleNo =  TR.VehicleNo;
                     
 
                     INSERT INTO tempDRS  (RunsheetNo,  DriverId, FacilityAddress,   Latitude, Longitude   ) 
                     SELECT _RunsheetNo,  _DriverId,
                     CONCAT(Address1, ',  ',City)Facility, Latitude, Longitude
                     FROM LocationMaster   LIMIT 1;
                
                    INSERT INTO tempDRS (
                     Id, RunsheetNo,  AWBNo,  City
                     ,  DriverId,  DriverName ,   Status ,  DriverPhoneNo 
                     , PaymentMethod, CollectableAmount,   FacilityAddress,  PhoneNumber1 
                     ,  Consignee,  ConsigneeAddress1, Latitude, Longitude,  InsertedOn,  InsertedBy,LocationCode,  VehicleNo  ) 
                    SELECT   
                    DL.Id,  DH.RunsheetNo, DL.AWBNo, IFNULL(SD.City,  '') AS City
                    ,  DL.DriverId, concat ( DL.DriverId ,' -  ',DM.Name )as DriverName, DL.Status
                     , DM.PhoneNo as  DriverPhoneNo, '' AS PaymentMethod , 0  AS CollectableAmount, '' AS  FacilityAddress
                    , ''  PhoneNumber1, '' Consignee
                     , concat(SD.City, ', Block:  ',SD.Block, ', Street: ',SD.Street, ',  Country: ', SD.Country)   ConsigneeAddress1
                    ,  IFNULL(SD.Latitude,'0')Latitude,  IFNULL(SD.Longitude,'0')Longitude
                     , DL.InsertedOn,  DH.InsertedBy, DL.LocationCode,  DH.VehicleNo
                    FROM  DRSHeader DH 
                    INNER  JOIN  DRSLine DL ON DL.RunsheetNo =  DH.RunsheetNo 
                    INNER  JOIN tempDRSJson TR ON TR.RunsheetNo=  DH.RunsheetNo
                    INNER  JOIN UserMaster DM ON DL.DriverId =  DM.Id 
                    INNER JOIN  ShipmentData SD ON DL.AWBNO = SD.AWBNo 
                     ORDER BY SD.Longitude  ASC;  
 
                   SELECT SRNO,  Id, RunsheetNo,  AWBNo,  City
                     , Consignee,   ConsigneeAddress1,  IFNULL(Latitude,0)Latitude
                     , IFNULL(Longitude,0)Longitude,  PhoneNumber1 
                    ,  DriverId,  DriverName ,  DriverPhoneNo ,   Status 
                    ,  PaymentMethod, CollectableAmount,   FacilityAddress
                    ,  InsertedOn,  InsertedBy, LocationCode,  VehicleNo  
                    FROM  tempDRS   ORDER BY SRNO;  
                 
                     
                 
                END;
                 END IF; 
            
    END;
    END  IF;
      

END