Fortunately there is another solution that allows a direct query of a stored procedure's result set without having to build temp tables (thanks to Erland Sommarskog for the info).
OPENQUERY allows you to query a stored procedure in the same way you query a base table, similar to querying a query in MS Access, and even allows joins between different sps.
For example, if I have two stored procs on my local server, PaymentUpsert and MilestoneUpsert, I can run the following select statement using OPENQUERY:
FROM OPENQUERY(LT010, 'EXEC StagingArea.dbo.PaymentUpsert') AS p
JOIN OPENQUERY(LT010, 'EXEC StagingArea.dbo.MilestoneUpsert') AS m
ON p.[Milestone__r:External_Id__c] = m.External_Id__c
WHERE p.Status__c = 'Completed';
If you get an error telling you that your server instance is not configured for data access then you can run the following query to allow access:
EXEC sp_serveroption 'LocalServerName', 'DATA ACCESS', TRUE;
Be aware that setting your local server data access to true may have security implications for you and your network. See data acess article on technet and also article on openquery.