SQL.Fetch

        So I was working on the eSupplier portal and I had a request for an enhancement to the Bidder Approval process. Before a bidder can access the eSupplier Portal they must first be approved by an Approver in the Procurement Department once this happens a user is granted roles to allow the user access to the eSupplier Portal. First I came up with adding a SQLExec statement placed in the correct location will insert the role and everything would be great, well maybe not. 
       As it turns out that a bidder when registering can add multiple users to the bidder profile and there is the issue. SQLExec only updates one row, the first row returned. With the following code I was able to update all of the users once the Approver from the Procurement Department clicked the Approval push button. 


/* Adding Custom Role for bidder access */
 
   Local SQL &SQL;
   Local string &OPRID;
   Local string &BIDDER_ID;

/* &RoleGrntOprid is the variable used during the processing of the program*/

 
   &OPRID = &RoleGrntOprid;

/* Select Bidder ID of the Users we want to Insert the Role for*/
 
   SQLExec("Select BIDDER_ID FROM PSOPRALIAS WHERE OPRID = :1", &OPRID, &BIDDER_ID);

/* Select Operator Id's associated with the Bidder ID*/
 
   &SQL = CreateSQL("Select OPRID FROM PSOPRALIAS WHERE BIDDER_ID = :1", &BIDDER_ID);

/*While I have Operator Id's associated with the Bidder Id Insert the row*/
 
   While &SQL.Fetch(&OPRID)
   
      SQLExec("INSERT INTO PSROLEUSER (ROLEUSER, ROLENAME, DYNAMIC_SW) VALUES(:1, 'BIDDER_ACCESS', 'Y')", &OPRID);
   
   End-While;
   
And this is how I solved the issue of inserting roles for multiple users associated with a Bidder Id.