Using Stored Procedures in SQL Server that return several results.

Stored Procedures give you more freedom than functions, and so they would be the obvious way of developing processes in SQL Server. There is one longstanding problem with them though, although it is possible to send several results to the application from a stored procedure and read them without problems, you have big problems capturing more than one in SQL Server. Even that one has to be done with care because of the problems with nested  INSERT…EXECUTE statements. You cannot have a procedure  that does an INSERT-EXEC operation call another one that also does an INSERT-EXEC operation.

All these problems can now be neatly bypassed in SQL Server 2016 and later due to JSON support. Actually you could do it before then using XML, if you cared for that sort of thing. I can tell you it was a bit messy.

Here is a way of capturing as many results from a stored procedure as you want. To demonstrate, I have taken an example of a stored procedure producing two results from MSDN, and modified it slightly. To demonstrate that you really have full control over the result, I save both as a SQL Relational table. I use temporary tables in this example but you can do it any way you like.

We run it and voila!!