I need to return partial result (as simple select) from a Stored procedure before it is finished.

Is it possible to do that?

If yes, how to do that?

If not, any workaround?

EDIT: I have several parts of the procedure. In the first part I calculate several string. I use them later in the procedure to make addtional operations. The problem is that string is needed by the caller as soon as possible. So I need to calculate that string and pass it back (somehow, from a select for example) and then continue to work. The caller gets its valuable string much more quickly.

Caller is a Web Service.

  • Assuming a complete table lock hasn't occurred or an explicit transaction hasn't been declared, you should be able to run the SELECT in a separate session without issue. Mar 18, 2016 at 17:27
  • In general this is only way I see it now, but I don't think it will be much faster (also there are other issues), @SteveMangiameli Mar 18, 2016 at 17:37
  • Split it into two SP? Pass the output from the first to the second.
    – paparazzo
    Mar 18, 2016 at 18:14
  • Not very quick solution, thats why we disaceded it, @Paparazzi Mar 19, 2016 at 10:52

4 Answers 4


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.

  • FAST solved the issue for me in an issue where I was needing to synchronize execution of a stored procedure and C# code to exacerbate and reproduce a race condition. It is easier to consume resultsets programmatically than use something like RAISERROR(). When I started reading your answer, it seemed like you were saying that it can’t be done with SELECT, so maybe that could be clarified?
    – binki
    Feb 27, 2019 at 18:55

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 level1 set to enable it to read uncommitted changes:

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:

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...

  • 1
    This is one option but I don't like it at this moment. I hope some other options will pop up. Mar 18, 2016 at 17:53

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)
        SELECT * FROM [##MyProcess_{0}];

    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.


    • 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:

    PROCEDURE #GetSomeInfoBackQuickly
      @MessageTableName NVARCHAR(50) -- might not always be a GUID
    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);';
    EXEC sp_executesql
        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
        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';
    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.

  • 2
    I try that. After calculating string I return it as simple select and continue the procedure. The problem is that it returns all sets at the same time (I suppose after RETURN statement). So it is not working. Mar 18, 2016 at 17:43
  • 2
    @BogdanBogdanov Are you using .NET and SqlConnection? How much data do you want to pass back? What datatypes? Have you tried either PRINT or RAISERROR WITH NOWAIT? Mar 18, 2016 at 17:47
  • I will try now. We use .NET Web Service. Mar 18, 2016 at 17:51
  • "Because it is a global temporary table, you don't need to worry about transaction isolation levels" - is that really correct? IIRC temporary tables, even global ones, should be subject to the same ACID restrictions as any other table. Could you detail how you tested the behaviour? Mar 21, 2016 at 9:20
  • @DavidSpillett Now that I think about it, the isolation level is really a non-issue, and the same holds true with regards to your suggestion. As long as the table is not created within a Transaction. I just updated my answer with the example code. Mar 22, 2016 at 7:04

If your stored procedure needs to run in the background (ie asynchronously), then you should use Service Broker. It's a bit of a pain to set up, but once done, you will be able to kick off the stored procedure (non-blocking) and listen to progress messages for as long (or as little) as you want.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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