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,CUSTO MERID,BOL, CustomerNa me,IMageID ,ImageType ID,TypeDes cription)
Select distinct B.FBID,B.POD,B.UnLoadDate, B.Customer ID,0 As BOL,C.CustomerName,I.Image ID,IT.Imag eTypeID,
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
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,CUSTO
Select distinct B.FBID,B.POD,B.UnLoadDate,
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER