looping a cursor in a looped cursor

• stev5k

  SSC Enthusiast

  Points: 124

  More actions

  February 6, 2004 at 1:43 pm

  #83812

  Hi All,

  I looked around but didn't find any reference to my problem. I have a situation where I need to loop thru and process multiple master records while looping thru multiple detail records of each of the master records.

  The procedure is hanging and I dont know the syntax for looping a cursor in a looped cursor. The procedure is more complicated but something like this:

  declare @ID int, @OrderID int, @OrderAmount float, @UserFetchStat int, @OrderFetchStat int

  declare cursor for curGetUsers

   select ID from People

  declare cursor for cur GetOrders

   select OrderID , OrderAmount  from Orders

  open curGetUsers

  fetch next from curGetUsers into @ID

  select @UserFetchStat = @@FETCH_STATUS

  while  @UserFetchStat = 0

  BEGIN

          open GetOrders

          select @OrderFetchStat = @@FETCH_STATUS

          while @OrderFetchStat = 0

              fetch next from curGetOrders into @OrderID, @OrderAmount

                  insert into UserSales (UserID, Sale) values @ID, @OrderAmount

              fetch next from curGetOrders into @OrderID, @OrderAmount

              select @OrderFetchStat = @@FETCH_STATUS

      END

      update sales set UserSales = (select sum(OrderAmount) where UserID = ID

  fetch next from curGetUsers into @ID

  select @UserFetchStat = @@FETCH_STATUS

  Thanks for any help.

• noeld

  SSC Guru

  Points: 96590

  More actions

  February 6, 2004 at 3:21 pm

  #493170

  It really looks that you DON'T need cursors For that!!!

  you may want something Like:

  SELECT p.ID, Sum(o.OrderAmount)

  FROM  People p Left outer join Orders o on p.ID = o.UserId

  Group by p.ID

   

  
  * Noel

• stev5k

  SSC Enthusiast

  Points: 124

  More actions

  February 6, 2004 at 3:58 pm

  #493179

  Thanks for the help.

  Arnold Schwartzenegger just terminated our program. Today is my last day so its a wash anyway.