Using “insert…exec” with a SP which returns 2 resultsets?

Question

Post reply

Using “insert…exec” with a SP which returns 2 resultsets?

Chris Colie-486731

Grasshopper

Points: 23
More actions
September 7, 2007 at 12:11 pm

#118599

Is there a way to use “insert…exec” to insert the 1^st resultset of an SP into a table when the SP returns 2 resultsets?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Very Tricky but doable (in some cases)

---=====================================

--- sp_helpdb returs two resultsets

---====================================

create table #t1 (

namenvarchar(24) null,

db_sizenvarchar(13) null,

ownernvarchar(24) null,

dbidsmallint null,

createdchar(11) null,

statusvarchar(340) null,

compatibility_leveltinyint null)

insert into #t1 (

name,

db_size,

owner,

dbid,

created,

status,

compatibility_level

)

SELECT a.*

FROM OPENROWSET('SQLOLEDB', 'Server=(local);Trusted_Connection=yes;', 'SET FMTONLY OFF; exec sp_helpdb master') AS a

select * from #t1

---=====================================

--- this shows you the "first"

---====================================

* Noel

Matthew Bailie SSC Veteran Points: 221 More actions · Answer 2

That gets the first record set, what about the 2nd? (I'm assuming this was the point of the question after all?)

I'm unaware of any "tidy" solution to this myself and instead use what I feel is quite untidy:

Create 2 temporary tables to hold the results

Then call a SP that populates those tables

-- The scope of a temporary table extends to stored procedures called later.

-- (They are out of scope after the SP they were defined in finishes.)

-- (Table Variables [@temp1] do not behave in this way.)

The reason this is extremely untidy is that the tables must be defined in the "outer stored procedure". As the "inner stored procedure" (which populates the tables) is likely to be used by more than one "outer stored procedure", those Temporary Tables will have definitions in several places. If you ever need to change the defintions you need to hunt around for all the SPs that create them...

CREATE PROCEDURE myInnerSP

AS

BEGIN

INSERT INTO #temp1 VALUES (1)

INSERT INTO #temp2 VALUES (2)

END

CREATE PROCEDURE myOuterSP

AS

BEGIN

CREATE TABLE #temp1 (field1, field2...)

CREATE TABLE #temp2 (field1, field2...)

EXEC myInnerSP

-- #temp1 and #temp2 now contain data

END

Mat.

noeld SSC Guru Points: 96590 More actions · Answer 3

That gets the first record set, what about the 2nd? (I'm assuming this was the point of the question after all?)

The Poster requested the 1st! If you need the second just create a table that matches the second and do a straight "insert..exec."

* Noel