How to share data between stored procedures ? Good Link

http://www.sommarskog.se/share_data.html



Discusses in detail various methods :

This table summarises the methods:

Method	Input/ Output	SQL Server versions	Comment
Using OUTPUT Parameters	Output	All	Not generally applicable, but sometimes overlooked.
Table-valued Functions	Output	SQL 2000	Probably the best method for output, but has some restrictions.
Inline Functions			Use this when you want to reuse a single SELECT.
Multi-statement Functions			When you need to encapsulate more complex logic.
Using a Table	In/Out	All	Most general methods with no restrictions, but a little more complex to use.
Sharing a Temp Table			Mainly for single pair of caller/callee.
Process-keyed Table			Best choice for many callers to same callee.
Global Temp Tables			A variation of Process-Keyed.
INSERT-EXEC	Output	SQL 6.5	Does not require rewrite. Has some gotchas.
Table Parameters and Table Types	In/(Out)	SQL 2008	Could have been the final answer, but due to a restriction it is only mildly useful in this context.
Using the CLR	Output	SQL 2005	Does not require a rewrite. Clunky, but is useful as a last resort when INSERT-EXEC does not work.
OPENQUERY	Output	SQL 7	Does not require rewrite. Tricky with many pitfalls.
Using XML	In/Out	SQL 2005	A roundabout way that requires you to make a rewrite, but it has some advantages over the other methods.
Using Cursor Variables	Output	SQL 7	Not recommendable.

Have a look at other articles also on the home page :

http://www.sommarskog.se/index.html

The curse and blessings of dynamic SQL. How you use dynamic SQL, when you should - and when you should not. (Added June 2001, Completely revised: April 2006. Most recent update: December 2008.) German translations of an old version available.

Arrays and Lists in SQL Server. Several methods on how to pass an array of values from a client to SQL Server, and performance data about the methods. There are three articles: one for SQL 2008 which is devoted entirely to table-valued parameters, one for SQL 2005 and later (for situations when you cannot use TVPs), and one for SQL 2000 and earlier. (Added March 2003. Version for SQL 2005 added March 2007, major revision and addition of article for SQL 2008: January 2010. Most recent update October 2010.)

Dynamic Search Conditions. How to write a stored procedure that permits users to select among many search conditions, using both dynamic and static SQL. There are two articles: one for SQL 2008, and one for SQL 2005 and earlier. (Added May 2003. Most recent update: November 2009. French and German translations of the SQL 2005 version available.)

How to share data between stored procedures. Different ways of passing sets of data between stored procedures. (Added July 2001, Most recent update: January 2010)

Giving Permissions through Stored Procedures. SQL 2005 adds two new methods – signing with certificates and impersonation with EXECUTE AS – that can manage cases where the classic method of ownership chaining fails. I explain the two new methods, as well as the old one, and warn you about the pitfalls. (Added February 2006)

Slow in the Application, Fast in SSMS? Ever experienced the situation that your query runs slow in your application, but when you try it in SQL Server Management Studio, the response is immediate? This article explains why this may happen, what parameter sniffing is and suggests methods to analyse and address your performance problem. (Added February 2011.)

Build Your Own Index. This is a teaser for.. Well, follow the link and read on! (Added November 2009.)

Error Handling in SQL 2005 and Later. This is an unfinished article, which consists only of a jumpstart section that shows how to use BEGIN-TRY. The full article will appear ...sometime. The article also gives you links to my old articles for error handling in SQL 2000. (Added July 2003. Most recent update: November 2009 )

Connecting to MS SQL Server from Unix. A brief summary of what's available. Since I don't work with Unix myself, it's not extremely well-informed, but people have kept asking me about this. (Added Jan 1999, Latest update: June 2008)