How to get response from stored procedure before it finishes?

You probably are looking for the RAISERROR command with the NOWAIT option.

Per the remarks:

RAISERROR can be used as an alternative to PRINT to return messages to calling applications.

This doesn't return the results from a SELECT statement, but it will let you pass messages/strings back to the client. If you want to return a quick subset of the data you're selecting then you might want to consider the FAST query hint.

Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.

Added by Shannon Severance in a comment:

From Error and Transaction Handling in SQL Server by Erland Sommarskog:

Beware, though, that some APIs and tools may buffer on their side, thereby nullifying the effect of WITH NOWAIT.

See the source article for the full context.

The O.P. has already tried sending multiple result sets (not M.A.R.S.) and has seen that it does indeed wait for the Stored Procedure to complete before returning any result sets. With that situation in mind, here are some options:

1. If your data is small enough to fit within 128 bytes, you could most likely use SET CONTEXT_INFO which should make that value visible via SELECT [context_info] FROM [sys].[dm_exec_requests] WHERE [session_id] = @SessionID;. You would just need to execute a quick query before you run the Stored Procedure to SELECT @@SPID; and grab that via SqlCommand.ExecuteScalar.

   I just tested this and it does work.

2. Similar to @David's suggestion to put the data into a "progress" table, but without needing to mess with cleanup or concurrency / process-separation issues:

   1. Create a new Guid within the app code and pass it as a parameter to the Stored Procedure. Store this Guid in a variable as it will be used several times.
   2. In the Stored Procedure, create a Global Temporary Table using that Guid as part of the table name, something like CREATE TABLE ##MyProcess_{GuidFromApp};. The table can have whatever columns of whatever datatypes you need.

   3. Whenever you have the data, insert it into that Global Temp Table.

   4. In the app code, start attempting to read the data, but wrap the SELECT in an IF EXISTS so it won't fail if the table hasn't been created yet:

      IF (OBJECT_ID('tempdb..[##MyProcess_{0}]')
          IS NOT NULL)
      BEGIN
        SELECT * FROM [##MyProcess_{0}];
      END;
      

   With String.Format(), you can replace {0} with the value in the Guid variable. Test for if Reader.HasRows, and if true then read the results, else call Thread.Sleep() or whatever to then poll again.

   Benefits:

   • This table is isolated from other processes since only the app code knows of the specific Guid value, hence no need to worry about other processes. Another process will have its own private global temp table.
   • Because it is a table, everything is strongly typed.
   • Because it is a temporary table, when the session executing the Stored Procedure ends, the table will get cleaned up automatically.
   • Because it is a global temporary table:
     • it is accessible by other Sessions, just like a permanent table
     • it will survive the ending of the sub-process in which it is created (i.e. the EXEC / sp_executesql call)

   
   I have tested this and it works as expected. You can try it for yourself with the following example code.

   In one query tab, run the following, and then highlight the 3 lines in the block-comment and run that:

   CREATE
   --ALTER
   PROCEDURE #GetSomeInfoBackQuickly
   (
     @MessageTableName NVARCHAR(50) -- might not always be a GUID
   )
   AS
   SET NOCOUNT ON;
   
   DECLARE @SQL NVARCHAR(MAX) = N'CREATE TABLE [##MyProcess_' + @MessageTableName
                + N'] (Message1 NVARCHAR(50), Message2 NVARCHAR(50), SomeNumber INT);';
   
   -- Do some calculations
   
   EXEC (@SQL);
   
   SET @SQL = N'INSERT INTO [##MyProcess_' + @MessageTableName
   + N'] (Message1, Message2, SomeNumber) VALUES (@Msg1, @Msg2, @SomeNum);';
   
   DECLARE @SomeNumber INT = CRYPT_GEN_RANDOM(2);
   
   EXEC sp_executesql
       @SQL,
       N'@Msg1 NVARCHAR(50), @Msg2 NVARCHAR(50), @SomeNum INT',
       @Msg1 = N'wow',
       @Msg2 = N'yadda yadda yadda',
       @SomeNum = @SomeNumber;
   
   WAITFOR DELAY '00:00:10.000';
   
   SET @SomeNumber = CRYPT_GEN_RANDOM(3);
   EXEC sp_executesql
       @SQL,
       N'@Msg1 NVARCHAR(50), @Msg2 NVARCHAR(50), @SomeNum INT',
       @Msg1 = N'wow',
       @Msg2 = N'yadda yadda yadda',
       @SomeNum = @SomeNumber;
   
   WAITFOR DELAY '00:00:10.000';
   GO
   /*
   DECLARE @TempTableID NVARCHAR(50) = NEWID();
   RAISERROR('%s', 10, 1, @TempTableID) WITH NOWAIT;
   
   EXEC #GetSomeInfoBackQuickly @TempTableID;
   */
   

   Go to the "Messages" tab and copy the GUID that was printed. Then, open another query tab and run the following, placing the GUID you copied from the other Session's Messages tab into the variable initialization on line 1:

   DECLARE @TempTableID NVARCHAR(50) = N'GUID-from-other-session';
   
   EXEC (N'SELECT * FROM [##MyProcess_' + @TempTableID + N']');
   

   Keep hitting F5. You should see 1 entry for the first 10 seconds, and then 2 entries for the next 10 seconds.

3. You could use SQLCLR to make a call back to your app via a Web Service or some other means.

4. You could maybe use PRINT / RAISERROR(..., 1, 10) WITH NOWAIT to pass strings back immediately, but this would be a little tricky due to the following issues:

   • "Message" output is restricted to either VARCHAR(8000) or NVARCHAR(4000)
   • Messages are not sent in the same manner as results. In order to capture them you need to set up an event handler. In that case, you could create a variable as a static collection to get the messages that would be available to all parts of the code. Or maybe some other way. I have an example or two in other answers here showing how to capture the messages and will link to them later when I find them.

   • Messages, by default, are also not sent until the process completes. This behavior, however, can be altered by setting the SqlConnection.FireInfoMessageEventOnUserErrors Property to true. The documentation states:

     When you set FireInfoMessageEventOnUserErrors to true, errors that were previously treated as exceptions are now handled as InfoMessage events. All events fire immediately and are handled by the event handler. If is FireInfoMessageEventOnUserErrors is set to false, then InfoMessage events are handled at the end of the procedure.

     The downside here is that most SQL errors will no longer raise a SqlException. In this case, you need to test additional event properties that are passed into the Message Event Handler. This holds true for the entire connection, which makes things a bit trickier, but not unmanageable.

   • All messages appear at the same level with no separate field or property to distinguish one from the other. The order in which they are received should be the same as how they are sent, but not sure if that is reliable enough. You might need to include a tag or something that you can then parse on. That way you could at least be certain which one is which.

UPDATE: See strutzky's answer (above) and the comments for at least one example where this does not behave as I expect and describe here. I will have to experiment/read further to update my understanding when time permits...

If your caller interacts with the database asynchronously or is threaded/multi-process, so you can open a second session while the first is still running, you could create a table to hold the partial data and update that as the procedure progresses. This can then be read by a second session with the transaction isolation level¹ set to enable it to read uncommitted changes:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM progress_table

^{1: as per the comments and subsequent update in srutzky's answer, setting the isolation level is not required if the process being monitored is not wrapped in a transaction, though I tend to set it out of habit in such circumstances as it doesn't cause harm when not needed in these cases}

Of course if you could have multiple processes operating this way (which is likely if your web server accepts concurrent users and it is very rare for that not to be the case) you'll need to identify the progress information for this process in some way. Perhaps pass the procedure a freshly minted UUID as a key, add that to the progress table, and read with:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM progress_table WHERE process = <current_process_uuid>

I've used this method to monitor long running manual processes in SSMS. I can't decide whether it "smells" too much for me to consider using it in production though...