Link to home
Start Free TrialLog in
Avatar of soccerman777
soccerman777

asked on

Stored procedure Cursor loop not iterating

I have a cursor loop within a stored procedure and it does not appear to by iterating.n The original querry returns 4 records but my insert is only inserting onece. So it looks like @BOLFBID is not iterating correctly. Here is my code.



BEGIN      
            DECLARE @BOLFBID int      
            DECLARE BOLCursor CURSOR FOR       
      
                  select distinct FBID from tblbillproducts where bol=@bol

            OPEN BOLCursor
            FETCH NEXT FROM BOLCursor INTO @BOLFBID
            WHILE @@FETCH_STATUS = 0
            BEGIN            
                  ---First we will delete all records in the temp table that are associated with this FBID #
                  delete from tbltempbol where FBID=@BOLFBID
            
                  -- insert into a flat table because there is no way to relate the imagesid up with the correct bol number
                  Insert into tbltempBOL (FBID,POD,UNLOADDATE,CUSTOMERID,BOL,CustomerName,IMageID,ImageTypeID,TypeDescription)
                  Select distinct B.FBID,B.POD,B.UnLoadDate,B.CustomerID,0 As BOL,C.CustomerName,I.ImageID,IT.ImageTypeID,
                  IT.TypeDescription
                  FROM tblBill AS B  
                  inner join tblImages AS I on (B.POD = I.Index1 OR CAST(B.FBID AS NVARCHAR(255)) = I.Index1  AND I.ImageGroupID='FB')
                  inner join tblImageTypes AS IT on (I.ImageTypeID = IT.ImageTypeID)
                  inner join tblCustomers AS C  on(B.CustomerID = C.CustomerID  )
                  inner join tblBillProducts BP on(B.FBID=BP.FBID)
                  where  b.FBID=@BOLFBID

            



            
            
            FETCH NEXT FROM BOLCursor INTO  @BOLFBID
      END
      CLOSE BOLCursor
      DEALLOCATE BOLCursor
ASKER CERTIFIED SOLUTION
Avatar of Nathan Riley
Nathan Riley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of soccerman777
soccerman777

ASKER

Actually I did not give you enough code to figure out what the problem was. I had declared @BOL as nvarchar  I did not declare nvarchar(20) like I should have. So when I inputed 90975 it was only seeing 9. But since I put you at disadvantage you get the soultion points.