title: MYSQL Stored procedure throwing Error [1644] [45000]: Variable MESSAGE_TEXT can't be set to the value of NULL
body: <p>Every time i execute mysql stored procedure i getting <strong>SQL Error [1644] [45000]: Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL'</strong></p>
  <p>To understand what exact i tried all the option but no luck</p>
  <p>Issue :</p>
  <ol>
  <li>Execute stored procedure first time - Success</li>
  <li>Execute stored procedure second time - Error</li>
  </ol>
  <p>Temp Workaround :</p>
  <ol>
  <li>Execute stored procedure first time - Success</li>
  <li>Put same blank space in non code area and save</li>
  <li>Execute stored procedure - Success</li>
  </ol>
  <p>I am not getting what exactly the issue and how to resolve it, if anyone can help will be appriciated.</p>
  <pre><code>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
  </code></pre>