Flush result set

Question

We have a stored procedure that returns 3 different result sets, and the first two take approx. 1 sec while the 3rd takes 20+ sec.

When running the SP in SSMS all 3 result sets are displayed at once after an initial delay of ~20 seconds.

Likewise when calling from .NET there is no delay between each call to IDataReader#NextResult, but there is a delay on the first call to IDataReader#Read.

If we modify the SP to stop after the first 2 result sets then the data is available immediately.

We want to start processing the results of the 1st 2 sets immediatly and not wait for all 3 result sets to be ready.

Is there a way to force SQL to flush the first 2 results sets so they can be consumed immediately?

user_12403612 · Answer

..for ssms…

--resultsets after 5secs
select 1;
select 2;
waitfor delay '00:00:05'; -- simulate a long running query
select 3;
go
--two resultsets and the last after 5secs 
select 1;
select 2;
raiserror('', 0, 0) with nowait; -- flush results
waitfor delay '00:00:05';  -- simulate a long running query
select 3;

Flush result set

1 Answers