Never tried this, but was fun learning this tip.

I wouldn't exactly call that nesting EXEC. This is really just wrapping dynamic SQL...

Now what would be really useful would be if you could put those two result sets into two separate tables. Or if you could do things like this:

IF EXISTS (EXEC SPName) ...

or

SELECT
FROM
   Table T
   OUTER APPLY (EXEC SPName T.Value) X

ErikEckhardt, the case you are describing can be done with the help of OPENROWSET Refer method 2 in this post http://beyondrelational.com/blogs/madhivanan/archive/2007/11/24/select-columns-from-exec-procedure-name-is-this-possible.aspx

Wow, great trick...Both styles seems to be great.

@Madhivanan, the problem with that is that in SQL Server 2005 and up, the server has to have the surface area configuration changed to allow ad-hoc distributed queries. The queries also have to be converted to dynamic SQL (and if you're doing that you could use temp tables anyway). Last of all, I think you can run into permission and permission chaining problems when using loopback queries.