Ok... here's a very simple demonstration of how it can be done with OPENROWSET. The following will execute sp_DirTree for C:\Temp and creates a temporary table from it using SELECT INTO.

Before you try to run this code change "serverrnamehere" and "instancenamehere" to the appropriate server\instance name.

--===== Conditionally drop temp tables to make reruns easier in SSMS.

IF OBJECT_ID('tempdb..#MyFiles','U') IS NOT NULL DROP TABLE #MyFiles;

--Create a table from the output of a stored procedure.

SELECT IDENTITY(INT,1,1) AS RowNum, *

INTO #MyFiles

FROM OPENROWSET('SQLOLEDB','Server=serverrnamehere\instancenamehere;Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.xp_DirTree ''C:\Temp'',1,1')

WHERE [File] = 1

ORDER BY SubDirectory

;

SELECT * FROM #MyFiles

;

Now, there are a couple of "problems" with this. First, whatever runs this needs sysadmin privs. That's normally not a problem for stored procedures using WITH EXECUTE AS OWNER. Of course, never give non-DBA users sysadmin privs. They should only do things by stored procedure.

You also need to allow ad hoc queries.

It also can't take variables. If you want to force it to take variables, you'll have to do it using dynamic SQL along with the normal advisory of sanitizing inputs to prevent SQL Injection.

If you can't do any of the sysadmin thing or enable ad hoc queries, the you might try OPENQUERY or OPENDATASOURCE in a similar fashion.

I'll also tell you that it actually runs the stored procedure twice, if I recall correctly. One to format the output and one to produce the output. Remember that if you need to guarantee that the stored procedure will only run once.