Guys,

The subject in discussion is not practical in an application development domain for obvious reasons.

From the perspective of DBA/Consultant I can imagine (HARDLY) that sometimes it would be interesting / impressive to show off how to use result set produced by execution of a stored procedure in a SUB-SELECT (you can't use EXECUTE in sub-query at the moment...).

I think that all the story with stored procedures and views on-a-fly - is pure scholastics in the best traditions of Old Testament and Talmud.

Below is a simplified example how to achieve the same results (assuming you have sysadmin credentials, of course). You can substitute system stored procedures with your own.

--Allow distributed queries first

use master

EXEC sp_configure 'show advanced options', 1

GO

reconfigure with override

GO

EXEC sp_configure 'allow updates',1

GO

reconfigure with override

GO

EXEC sp_configure 'Ad Hoc Distributed Queries',1

GO

reconfigure with override

GO

-- Run your "tricks"

SET NOCOUNT ON

SELECT

S.status,

S.dbName,

S.cmd,

O.name,

L.TYPE,

L.mode,

L.status

FROM

(SELECT * FROM OPENROWSET ( 'SQLOLEDB','Server=.\SQL2008;Database=master;Trusted_Connection=yes;','SET FMTONLY OFF EXEC SP_WHO')) S,

(SELECT * FROM OPENROWSET ( 'SQLOLEDB','Server=.\SQL2008;Database=master;Trusted_Connection=yes;','SET FMTONLY OFF EXEC SP_LOCK')) L,

sys.objects O

WHERE

L.OBJID = O.object_id

ANDS.SPID = L.SPID

GO

--Cleanup after yourself

EXEC sp_configure 'Ad Hoc Distributed Queries', 0

GO

reconfigure with override

GO

EXEC sp_configure 'allow updates', 0

GO

EXEC sp_configure 'show advanced options', 0

GO

reconfigure with override

GO