SQL Server 2000 "yield return" result behavior
===

I've got a very long query for our SQL Server 2000 database, and I am returning the results for further processing by my C# application.

Consider this query:


    declare @sn varchar(20)

    select distinct serial_number 
    from manufacturingData 
    where '7/19/2012'<date_time
    order by serial_number

    while (0 < (select count(serial_number) from #sn)) begin
      select top 1 @sn=serial_number from #sn
      exec sp_GetSnData @sn
      delete from #sn where serial_number=@sn
    end
    drop table #sn

I am writing this query on `07/19/2012`, so it is only returning records from today.

The query above (for half of today), returned 119 distinct serial numbers and took 52 seconds to execute with the while loop that accompanies it.

Typically, this report is executed for a 30-day period, and there are around 3500 part numbers.

Is there a way to "hook into" the result set so that I can have access to the results as they are generated?

The way it is now, no one knows if the query is going to take 2 minutes or 2 days ...or if it is even still doing anything! Does the manager shut down his computer and go home for the day or is this report query just about done?

Once the individual tables are returned, then I need to process each table individually.

It would be nice if I could

- find out how many distinct items were returned,
- what item is currently being queried, and
- have the resulting table available to process while waiting for the next table to come in.

Is this possible?

FYI: I currently do this in my Windows Form, by sending individual requests to our Server, but this is bogging the Server down and using up all of the available connections.