how can another procedure consume the data of a dynamic procedure, if it doesn't know what columns would be included?
typically, you create a temp table and insert into it, ie:
CREATE TABLE [dbo].[#results] (
[ic_recno] INT NOT NULL,
[ic_file_type] INT NOT NULL,
[ic_file_name] VARCHAR(200) NOT NULL,
[ic_last_retrieved] DATETIME NOT NULL,
[ic_last_executed] DATETIME NOT NULL,
[ic_file_size] INT NOT NULL,
[ic_file_dt] DATETIME NOT NULL,
[ic_include] INT NOT NULL,
[ic_imported] INT NOT NULL,
[ic_interval] INT NOT NULL,
[ic_file_key] VARCHAR(50) NULL)
INSERT INTO #Results
EXEC MyProcedure
so it would really require you to know the columns/shape oif the data in advance.
you can do it a little more dynamically with a linked serer / loopback server, but i doubt that's what you really need.
--note that the . will map to teh current default instance on a server, so it might be .\SQL2008R2, for example
EXEC master.dbo.sp_addlinkedserver @server = N'LOOPBACK',@srvproduct = N'', @datasrc = N'.', @provider = N'SQLOLEDB';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LOOPBACK',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;
SELECT theColumnsYouNeed into #tmp
FROM OPENQUERY(LOOPBACK, 'EXEC dbo.usp_GetAnalysis @mode = ''mode'' , @group = ''group'' ')
SELECT * into #tmp2
FROM OPENROWSET('SQLOLEDB','Server=.;Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who')
Lowell