How to Share Data Between Stored Procedures

Question

There were a number of questions related to sharing data results of one stored procedure in another in MS SqlServer.

Depending of the version of SQL Server people would suggest using temporary tables, xml (SQLServer 2005) or table variables (SQL Server 2008).

There is a great article written by Erland Sommarskog that provides comprehensive answer and list all options available in different versions of SQL:

• How to Share Data Between Stored Procedures

I thought it was worth sharing.

I came across that article when reading an answer by deevus suggesting the use of INSERT-EXEC Statement, something that I was not really familiar with before

Answer 1

There is a great article written by Erland Sommarskog that provides the comprehensive answer and list all options available in different versions of SQL:

• How to Share Data Between Stored Procedures

This article tackles two related questions:

• How can I use the result set from one stored procedure in another, also expressed as How can I use the result set from a stored procedure in a SELECT statement?
• How can I pass a table as a parameter from one stored procedure to another?

In this article I will discuss a number of methods, and also point out their advantages and drawbacks. Some of the methods apply only when you want to reuse a result set, whereas others apply in both situations. In the case you want to reuse a result set, most methods require you to rewrite the stored procedure in one way or another, but there are some methods that do not.