How can I use a recordset out from a stored procedure in another stored procedure?

In SQL Server 2016, there's a stored procedure that has an EXEC statement.  I want to use the recordset output from that stored procedure in another stored procedure, but due to the limitations of SQL Server I can't use it in a FROM clause, and I also can't Insert the recordset into a table variable (can't have nested EXEC's).  I also can't put the programming of the original stored procedure into a table valued function so I can use the output recordset in a FROM clause because you can't use EXEC in a function.  How can I use the recordset in a stored procedure?  Is a temporary table an option?
LVL 1
Declan_BasileITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
So, if you have SP2 that runs the EXEC to produce a resultset, and SP1 that needs the resultset does that mean SP1 executes SP2 ?

In which case if you create a temp table in SP1, then that temp table can be populated with resultsets, by SP2 within SP2, or, can populate by calling SP2.  So long as you know the columns being returned

CREATE TABLE #SP2_Results (<column definitions>);
INSERT INTO #SP2_Results
EXEC SP2

Open in new window


Or when you execute you can specify 'WITH RESULT SETS'
EXEC SP2 
With Result Sets
(<column definitions>)

Open in new window


Would that type of approach work for you ? If not, then we need a bit more discussion as to your needs.

Unfortunately, the dynamic SQL in SP2 will mean selecting from that SP wont work
SELECT * INTO #SP2_Results FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;','EXEC ee.dbo.SP2')

Open in new window

will generate errors and recommend WITH RESULT SETS....
0
Declan_BasileITAuthor Commented:
I've tried this approach and get the error "An INSERT EXEC statement cannot be nested."
I've also tried adding "WITH RESULTS SETS".  This worked when just executing SP2 but I kept getting a syntax error and the procedure wouldn't compile when using both INSERT EXEC and WITH RESULT SETS.  Any ideas?
0
Declan_BasileITAuthor Commented:
I solved my problem by defining a local temporary table in the calling procedure and populating the data into the table in the called procedure.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Declan_BasileITAuthor Commented:
Outputing the data from the stored procedure and populating it into a temp table or table variable within the procedure that calls the stored procedure doesn't work because the stored procedure that is called has an execute statement within it.  The called stored procedure needs to populate the temporary table which has to be defined in the calling procedure.
0
Mark WillsTopic AdvisorCommented:
@declan, I did suggest that in my comment above - did you not see it  - or maybe it didnt make sense ?
0
Mark WillsTopic AdvisorCommented:
I did say "In which case if you create a temp table in SP1, then that temp table can be populated with resultsets, by SP2 within SP2"
 
and for the SQLNCLI, that you needed "with result sets" for it to work.

Below is 'proof' of what I had posted above....
-- create out SP2 with dynamic SQL

create procedure EE_SP2 @table varchar(100), @src char(3) = 'SP2'
as
begin
    declare @sql varchar(500)
	set @sql =  'select '''+@src+''' as src, table_name,column_name from INFORMATION_SCHEMA.COLUMNS where table_name like '''+@table+''''
    if object_id('tempdb..#sp2_results','U') is not null
	begin
	    insert #sp2_results
        exec (@sql)
	end
    else 
       exec (@sql)

end
go

-- now test

EXEC ee.dbo.EE_SP2 @table = '%', @src = 'SP2'

-- now create our calliing procedure SP1

create procedure EE_SP1
as 
begin

    if object_id('tempdb..#sp2_results','U') is not null drop table #sp2_results

	create table #sp2_results (CalledFrom varchar(10), table_name varchar(200), column_name varchar (200))
	
	EXEC ee.dbo.EE_SP2 @table = '%', @src = 'SP1'
    --with result sets ((src varchar(10), tname varchar(100),cname varchar(100)))

	select * from #sp2_results

end	 
go

-- and test SP1

exec ee.dbo.EE_SP1

-- note we commented out the withresultsets because we dont actually want anything from SP2 - we want it in the temp table

-- but we can use the result set option to select via an internal linked server

if object_id('tempdb..#sp2_results','U') is not null drop table #sp2_results

SELECT * INTO #SP2_Results FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;','EXEC ee.dbo.EE_SP2 @table = ''%'',@src=''SQL'' 
with result sets ((src varchar(10), tname varchar(100),cname varchar(100)))')

select * from #sp2_results

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Declan_BasileITAuthor Commented:
Yes you did.  I missed that.  Sorry.  I was looking more at the code sample:

1:CREATE TABLE #SP2_Results (<column definitions>);
2:INSERT INTO #SP2_Results
3:EXEC SP2

which looked to me like everything is done in SP1 and that line 3 is part of line 2.  I'll assign you the points, and thank you for the help.
0
Mark WillsTopic AdvisorCommented:
Yeah, I can see what / why the confusion.... Should have taken more care to code with clear annotations as to which part was which. The exec sp2 should have been exec (@sql) - They are meant to be quite separate  steps. Which is why I clarified in the second post. Sorry for the confusion, and thanks for your patience and understanding.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.