Stored procedure Cursor loop not iterating

Posted on 2008-11-13
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.

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

            OPEN BOLCursor
            WHILE @@FETCH_STATUS = 0
                  ---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,
                  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


      CLOSE BOLCursor
Question by:soccerman777
    LVL 11

    Accepted Solution

    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


    Author Closing Comment

    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.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now