My contemplations and diurnal novelties are publicized here


I have had trouble creating in Mysql cursor so i decide to post this.

Don’t use same name for cursor variable similar to Column name of the fetched recordset.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `INVESTOR2PROFILE`(
)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE _PID INT;
DECLARE _AID INT;
DECLARE _CIC ,_IN ,_rn ,_ct,_AN ,_AT,_OI ,_AOD, _ATI ,_E ,_ISP varchar(45);
DECLARE cur1 CURSOR FOR SELECT CNIC,InvestorName,registrationNo,categoryType,AccountNumber,AccountTitle,OperatingInstruction,AccountOpeningDate,
ATT.AccountTypeID, Email, 'y' IsPrimary from temptableforbulkimport temp left join AccountTypes ATT 
on temp.AccountType = ATT.AccountType;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  
  read_loop: LOOP
    FETCH cur1 INTO _CIC ,_IN  ,_rn ,_ct,_AN ,_AT,_OI ,_AOD, _ATI ,_E ,_ISP;
    IF done THEN
      LEAVE read_loop;
    END IF;
    Select _CIC ;
    Select _IN  ;
    Select _rn; 
    Select _ct;
    Select _AN ;
    Select _AT;
    Select _OI ;
    Select _AOD;
    Select _ATI ;
    Select _E ;
    Select _ISP;
    /**********************Create Profile**********************/
    INSERT INTO profiles(CNIC,ProfileName) VALUES (_CIC,_IN);    
        Set _PID = LAST_INSERT_ID();
    /**********************Create Account**********************/
        INSERT INTO accounts(AccountNumber ,AccountTitle ,OperatingInstructions ,AccountOpeningDate , AccountTypeID) values (_AN ,_AT,_OI ,_AOD, _ATI);
        Set _AID = LAST_INSERT_ID();
    /**********************Create ProfileAccount**********************/
    select _PID;
    select _AID;
    INSERT INTO profileaccount(AccountID ,ProfileID,IsPrimary, Email) values (_AID ,_PID,_IsPrimary, _E);
    /**********************Create ProfileInvestor**********************/
    INSERT INTO ProfileInvestor(ReferenceID ,InvestorCategory,ProfileID) values (_rn ,_categoryType,_PID);    
  END LOOP;

  CLOSE cur1;  
END
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: