Solved

Problem with SQL Server Cursors

Posted on 2003-10-22
8
346 Views
Last Modified: 2009-07-29
Hi,
I am nesting 2 cursors,
First Cursor retrieves ID's from 1 table,
Second(Inner) Cursor retrieves IDs from temp table
Within the Inner Cursor I do some evaluations and
Insert to a table.
Then when done, loop the outer cursor to the next ID and do the inner cursor stuff again.

I am experiencing problems with both Cursors at different times. Sometimes the Outer Cursor does not seem to finish looping through, leaving out the last ID, sometimes the Inner Cursor doesnt seem to finish. I can't for the life of me figure this out.

Weirdest thing is, when I step through this in the debugger in both Interdev and SQL Server Management Console, It runs perfectly. I only have 170 points so thats all I can offer, but if anyone has a sec that can peak at the following code and give me any ideas, I would greatly appreciate it. I have been working this problem from so many agles for 3 days now and can't get it to work right.

Is there any specific things to watch out for when nesting cursors? Could the @@FETCH_STATUS from the inner Cursor be screwing with the outer Cursor?

Thanks,

      
DECLARE RECUR_CURSOR CURSOR FOR
Select ID from Recurrence where eventID = @eventID
Open RECUR_CURSOR
FETCH NEXT FROM RECUR_CURSOR INTO @recurID
WHILE @@FETCH_STATUS = 0
   Begin
       
        Declare CalUser_Cursor Cursor FOR
        Select CalID from #CalUserID order by CalID asc
        Open CalUser_Cursor
        Fetch Next From CalUser_Cursor into @CalUID
        While @@Fetch_Status = 0

      Begin
                                                     Select @calCnt = count(ID) from TestTable
                     where RecurID =  recurID and CalID = @CalUID
      
                     IF @calCnt < 1
                                                       Begin
            Insert into TestTable(RecurID, CalID)
            Values(@recurID, @CalUID)
            End
      Fetch Next From CalUser_Cursor into @CalUID
      End                  
      Close CalUser_Cursor
                        
                              
   FETCH NEXT FROM RECUR_CURSOR INTO @recurID
   END
0
Comment
Question by:jschillm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 19

Accepted Solution

by:
Dexstar earned 85 total points
ID: 9603027
Just glancing at it, try changing this:

      Select @calCnt = count(ID) from TestTable
      where RecurID =  recurID and CalID = @CalUID

To This:

      Select @calCnt = count(ID) from TestTable
      where RecurID =  @recurID and CalID = @CalUID

But, I think that what you are trying to do can be done without cursors.  If you would accept an answer that works without cursors, can you explain more about what you are trying to do?

It looks like you are trying to insert into TestTable all the combinations of RecurID and CalID that are not in the table using only the values for RecurID that come from the ID field of the table Recurrence where eventID = @eventID, right?

I can make a statement that does that without using any cursors.  It should be more efficient.

Dex*

 
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9603198
jshillm,

Try this query:
      SELECT
            R.ID As RecID,
            C.CalID
      FROM
            Recurrence R,
            #CalUserID C
      WHERE
            R.ID NOT IN (SELECT RecurID FROM TestTable WHERE CalID = C.CalID)
      ORDER BY
            CalID ASC

It *SHOULD* return all the rows that need to be inserted into TestTable.  But its the end of the day, and I could've messed up the query.  If it works, then you can modify it to this, and you'll be done without any cursors at all.

      SELECT
            R.ID As RecID,
            C.CalID
      INTO
            TestTable
      FROM
            Recurrence R,
            #CalUserID C
      WHERE
            R.ID NOT IN (SELECT RecurID FROM TestTable WHERE CalID = C.CalID)
      ORDER BY
            CalID ASC

Hope this helps,
Dex*
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9603251
Okay, since I don't know if what I gave you will work or not, I went ahead and wrote up another approach.  If the other one doesn't work, try this one instead.

      SELECT
            R.ID As RecID,
            C.CalID
      FROM
            Recurrence R,
            #CalUserID C
      WHERE
            CHECKSUM(R.ID, C.CalID) NOT IN (SELECT CHECKSUM(RecurID, CalID) FROM TestTable)
      ORDER BY
            CalID ASC

But that will only work on SQL 2000.  Is that an issue?

Dex*
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 9

Assisted Solution

by:xenon_je
xenon_je earned 85 total points
ID: 9603446
Try to not use cursors where is not neccesary.....

your code can be rewriten without using cursors... I didn't tested the above sugestions..this is your job...

Here is my equivalent:

Insert into TestTable(RecurID, CalID)
Select
  distinct ID ,  CalID
from
  Recurrence r join (Select CalID from #CalUserID order by CalID asc) tmp
where
  eventID = @eventID
  and (select count(ID) from TestTable where recurid = r.ID and CalID = tmp.CalID) = 0

-- count = 0 is the same as count<1, but is faster

good luck,
  xenon
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9603512
jshillm,

Before, I forgot to filter on @eventID:
     SELECT
          R.ID As RecID,
          C.CalID
     FROM
          Recurrence R,
          #CalUserID C
     WHERE
          R.ID NOT IN (SELECT RecurID FROM TestTable WHERE CalID = C.CalID)
          AND
          R.EventID =  @eventID
     ORDER BY
          CalID ASC

You should use the query analyzer to try all of these suggestions.  Some of them may be faster that the others.

Dex*
0
 

Author Comment

by:jschillm
ID: 9607318
Hi Guys,

Thanks so much for your quick feed back, both answers pointed me in the right direction and helped out tons.

When I tested Dex's solution, it almost did what I wanted, it brought up all of the records that needed to be inserted, if none had been inserted for a given CALID, however if there were 5 RecurIDs and there was 1 RecurID associated with the CalID, it did not show the 4 recurIDs that still needed to be inserted. It did help me though and give me pointers,

I used Xenon Joe's ansswer with a little modification, and it gave me exactly what I was looking for. One thing though, I did get an error when having the 'Order By' clause in a subquery, so i took it out.

I would have like to have split the points, but I did not get the option when I accepted the answer, any way of doing this after the fact?

PS, Is it possible to Delete from a table using similar code above?
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9608266
I think you can split the points after the fact if you ask the moderators to help you.

Post a comment in Community Support and tell them what you'd like to do, and include a link to this question so they know which one to deal with.

Community Support
http://www.experts-exchange.com/Community_Support/

This Question
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20775204.html

Glad you got it working,
Dex*
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 9611189
yap....you can also delete using a code similar to the above... if you have problems post it here and I will help you solve it...

good luck,
  xenon

PS: thx  :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

737 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