This blog by Erland Sommarskog, SQL Server MVP discusses data sharing techniques between stored procedures in SQL.
Following are the topics discussed in this blog:
(source: http://www.sommarskog.se/share_data.html)
One should focus on the limitations of the queries (for example, methods of the above table) to be used and restrictions of SQL server before using any SQL queries.