Erland Sommarskog has an excellent SQL Server article “How to Share Data Between Stored Procedures” tackling these two 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?
He discusses several methods, and points out their advantages and disadvantages. [Adding here so I remember where to find it in future!]