How to Share Data Between Stored Procedures in MS SQL

Method	In/Out	Rewrite callee?	Required version	Comment
OUTPUT Parameters	Output	Yes		Not generally applicable, but sometimes overlooked.
Table-valued Functions	Output	Yes		Often the best choice for output-only, but there are several restrictions.
Inline Functions				Use this to reuse a single SELECT.
Multi-statement Functions				When you need to encapsulate more complex logic.
Using a Table	In/Out	Yes		The most general solution. My favoured choice for input/output scenarios.
Sharing a Temp Table				Mainly for a single pair of caller/callee.
Process-keyed Table				Best choice for many callers to the same callee.
Global Temp Tables				A variation of process-keyed.
Table-valued Parameters	Input	Yes	SQL 2008	Mainly useful when passing data from a client.
INSERT-EXEC	Output	No		Deceivingly appealing, but should be used sparingly.
Using the CLR	Output	No	SQL 2005	Complex, but useful as a last resort when INSERT-EXEC does not work.
OPENQUERY	Output	No		Tricky with many pitfalls. Discouraged.
Using XML	In/Out	Yes	SQL 2005	A bit of a kludge, but not without advantages.
Using Cursor Variables	Output	Yes		Not recommendable.

Read there ...