?
Solved

Stored procedure Cursor loop not iterating

Posted on 2008-11-13
2
Medium Priority
?
426 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:soccerman777
2 Comments
 
LVL 12

Accepted Solution

by:
Nathan Riley earned 2000 total points
ID: 22954196
This is the only part from the cursor that is affecting the insert, so it is only true one 1 row and now all 4 like before.
where  b.FBID=@BOLFBID

Open in new window

0
 

Author Closing Comment

by:soccerman777
ID: 31516535
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.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question