Inserting dynamic stored procedure result into table

Question

I am writing a stored procedure (SQL Server) which receives string argument which represents a name of other stored procedure. This string also contains all the stored parameter so in order to run it I just need to write EXEC @vSpCommand.

Now comes my problem: My procedure should be able to deal with versatile SPs so I can't know how will the result table would look like. Meaning I don't know how many columns the table would have, and the type of each column.

In addition inside the given SP (@vSpCommand) there might be another dynamic call for other stored. Therefore using OPENROWSET won't work. I have tried the following:

SET @vSpCommand = REPLACE(@vSpCommand,'''','''''')

SET @vCmd =  'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[TA_SpTable'+@vExecId+']'') AND type in (N''U''))
DROP TABLE TA_SpTable'+@vExecId+' SELECT * INTO TA_SpTable'+ @vExecId+' FROM OPENROWSET(''SQLOLEDB'', ''Server=SERVERNAME,3180;Trusted_Connection=Yes'',
''EXEC '+@vSpCommand+ ''')'

EXEC (@vCmd)

Without any success.

The question is how do I insert this SP's result into table?

Unless it is not possible, solution is preferred within SQL, else using .NET CLR is also an option.

Answer 1

Transform your SP into a function then use SELECT INTO statement:

CREATE FUNCTION MyFunction()
RETURNS TABLE
AS
RETURN SELECT 'a' as A, 'b' as B
GO

SELECT *
INTO MyTable
FROM MyFunction()
GO

SELECT * FROM MyTable
GO

Answer 2

You have to set ad-hoc permission in the database before you execute this

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Try this, the same way you tried but some changes in the code. You can easily get it.

DECLARE @vSpCommand NVARCHAR(500),@vCmd NVARCHAR(4000)

SET @vSpCommand = Replace('TestDB.dbo.spg_GetNames', '''', '''''')
SET @vCmd =   'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[TA_SpTable]'') AND type in (N''U''))
                    DROP TABLE TA_SpTable' 
            + ' select a.* into TA_SpTable from openrowset(''SQLNCLI'',''Server=.;Trusted_Connection=yes;'',''SET NOCOUNT ON;SET FMTONLY OFF; exec '+ @vSpCommand +''') AS a'

PRINT @vCmd
EXEC (@vCmd) 

select * from TA_SpTable

Just note that I have used the SP "spg_GetNames" from database "TestDB". You are free to make any change in the code and please comment if any help is required.