SQL Server – Using Nested Cursors for Multiple Result Sets

cursorssql server

I'm using a SQL Server stored procedure to generate an Excel report. Basically each "entry" in the report has three parts: Object, Observation, and Points. The observations have a reference to the primary key of an object, and the points have a reference to an observation. That is the relationship between the parts. (Sorry if my terminology is off, I am used to writing in C# or Java but still learning SQL.)

I want the output to be like this:

-Object

  -All Observations linked to that Object

    - Under each observation there should be all the points linked to that observation

And repeat for each object. I have this working using a cursor for generating all observations with each object, but I can't get the points to come up under each observation.

I have read all over the Internet that cursors are Bad, and I assume this means nested cursors are Very Bad. But this is what I have so far. If you know of a better way to do this, please tell me. I am open to totally refactoring this if necessary.

  DECLARE @Object_Cursor cursor
  DECLARE @Observation_Cursor cursor
  DECLARE @i int
  DECLARE @j int
  DECLARE @row int = 1
  SET @Object_Cursor = CURSOR FOR (SELECT PK_ObjectId FROM Objects)
  OPEN @Object_Cursor
  FETCH NEXT FROM @Object_Cursor INTO @i
    While @@FETCH_STATUS = 0
    Begin
    Select 
        PK_ObjectID,
        Name,
        Timestamp
    From Objects where PK_ObjectID = @i


  SET @Observation_Cursor = CURSOR FOR (SELECT @PK_ObservationID FROM Observations)
  OPEN @Observation_Cursor
  FETCH NEXT FROM @Observation_Cursor INTO @j
    While @@FETCH_STATUS = 0
    Begin
    Select
        Timestamp,
        Note,
        User
        from Observations
        where FK_ObjectId = @i
        order by FK_ObjectID

    Select Lat, Lng 
    From Pts
    Where FK_ObservationID = @j
    Fetch Next From @Observation_Cursor into @j
    END

    FETCH NEXT FROM @Object_Cursor into @i
    END

    Close @Observation_Cursor
    DEALLOCATE @Observation_Cursor
    CLOSE @Object_Cursor
    DEALLOCATE @Object_Cursor

I feel like the order I'm executing these loops is off somehow, but I haven't been able to get it. Current output is:

 -Object    
   -Observations for that object
    -empty points result set

These results are repeated several times for the same object before it goes to the next one, and the points are always empty.

Any help or pointers in another direction is much appreciated.

Best Answer

select obj.PK_ObjectID, obj.name, obs.timestamp, obs.note -- etc
from   Objects as obj
join   Pts     as obs
on     obj.PK_ObjectID = FK_ObservationID
order by obj.PK_ObjectID, obs.FK_ObservationID

You're googling the wrong places, if I may say so. You yourself say you're new to SQL, and yet you've figured out how to implement a cursor instead of writing an elementary JOIN. You wrote 33 lines where 5 will do, and ensured your query will run 1% as fast as it normally would.

I hearby award you one gold star for asking! Now get cracking with a good SQL book such as An Introduction to Database Systems (8th Edition), 2003, C.J. Date.

Related Question