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.