How to get response from stored procedure before it finishes?

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.

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.

StackExchange Database Administrators Q#132673, answer score: 11