0

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.

  • 1
    It cannot be said often enough or loud enough: Don't use Cursors for any SQL task until you have googled a half-dozen experts who agree that you must. This is most particularly true when learning SQL, so that you don't learn bad habits that will get you fired, or banned from every writing SQL again. Avoiding cursors will make your code more efficient (dramatically!), as well as making your code more concise and easier to both read and write. – Pieter Geerkens Jul 24 '14 at 20:39
  • Any advice on how I can accomplish something like this without using cursors? – eddie_cat Jul 24 '14 at 20:41
  • (1) Please provide a listing of the relevant fields, and all keys, in your three tables of interest. (2) Please post also an attempt to perform this task in non-cursor SQL. – Pieter Geerkens Jul 24 '14 at 20:44
  • I'm also of the opinion that you should pure SQL if at all possible, and only use cursors if there really is no other way. Having said that, do you have a problem with this code? Is performance OK? Working code is better than anything else. – Colin 't Hart Jul 24 '14 at 21:32
  • I sort of get the feeling that neither of you read my question--I know cursors aren't ideal, mentioned that. I also mentioned that while this code didn't throw errors, it gave the incorrect output. I didn't know how to not use cursors to do what I needed to do, hence the question. It's okay though, I found a way to do it that didn't even involve them. Just need more practice with SQL to get out of the C# mindset, I suppose. Thanks anyways. – eddie_cat Jul 25 '14 at 12:43
2
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.