INSERT/EXEC With multiple returned datasets

Question

Post reply

INSERT/EXEC With multiple returned datasets

Dark Angel

SSC Veteran

Points: 238
More actions
August 22, 2006 at 6:28 am

#94529

I have a stored procedure (SP1) that returns multiple data sets.

i.e., the last 3 lines read:

SELECT * FROM #category;

SELECT * FROM #menu;

SELECT * FROM #coupon;

I now have another stored proceudre (SP2) and need to access the returned table called 'menu' from SP1.

If I try:

CREATE TABLE #menu

( MenuID uniqueidentifier,

RestaurantID

uniqueidentifier,

MenuHoursID

uniqueidentifier )

INSERT #menu EXEC SP1

as the body for sp2, this does not work. However, if I remove the "SELECT * FROM #category" and "SELECT * FROM #coupon" out of SP1, it works great.

How do I make it access only the second dataset of SP1? Is there maybe a better way of doing this rather than a INSERT-EXEC? I cannot use a permenent table so I think I'm somewhat limited.

Thank you!

Viewing 10 posts - 1 through 9 (of 9 total)

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

Dark Angel SSC Veteran Points: 238 More actions · Answer 1

Dark Angel

SSC Veteran

Points: 238

August 23, 2006 at 10:45 am

#656420

Joe,

Go blow yourself.

Thank you.

John Beggs SSCommitted Points: 1921 More actions · Answer 2

I may be wrong but I don't think this is possible...

If it is possible, it certainly isn't recommended. I would highly recommend that you look at your options of solving this in another manner.

I don't know if you intended this or not, but please note that in your original post, you are using the temp table '#Menu' several times and in some pretty odd orders, making the 'code' that you posted pretty useless in coming up with an actual solution for you, if one was even possible.

Good luck

Yusuf Bhiwandiwala Ten Centuries Points: 1298 More actions · Answer 3

I am not very clear on your requirement. But this could be one solution

Execute SP1 from inside SP2 (where you have declared the #menu temporary table)

Populate the #menu table in SP1

No select statements in SP1

The #menu table in SP2 will reflect the updates that you have done on it in SP1.

Do tell me if i am wrong.

Yusuf

Ward Pond SSC Eights! Points: 999 More actions · Answer 4

Ward Pond

SSC Eights!

Points: 999

August 25, 2006 at 2:47 pm

#656951

I would suggest that your second sproc access the contents of the temp table rather than the results of a SELECT run against it. These steps would likely render your code both more maintainable and more performant.

Hope this helps.

Take care..

- Ward Pond
blogs.technet.com/wardpond

ron bennatan-205269 Valued Member Points: 65 More actions · Answer 5

Does someone have an answer fo rthe original question?

I need to call sp_helplogins which returns two different result sets - and I want the second.

I want to do somehting like:

create table #q1(a varchar(255), b varchar(255), c varchar(255), d varchar(255))

insert #q1 exec sp_helplogins

select a,b,c from #q1

drop table #q1

But I need the second result set not the second - how do I go there?

Ward Pond SSC Eights! Points: 999 More actions · Answer 6

Ward Pond

SSC Eights!

Points: 999

September 12, 2006 at 11:04 am

#659933

Hi Ron..

How about copying the pertinent code out of sp_helplogins and writing your own procedure?

Hope this helps..

Take care..

- Ward Pond
blogs.technet.com/wardpond

Thomas Drudge Newbie Points: 9 More actions · Answer 7

Thomas Drudge

Newbie

Points: 9

June 4, 2007 at 12:20 pm

#710684

I've got a similiar problem trying to grab the results from an extended stored proc from a third party app.

xproc = no access to code.

Proc returns two seperate data sets - the first with a single column, the second with two columns.

Any suggestions?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Hi Dark Angel

<< I have a stored procedure (SP1) that returns multiple data sets.

i.e., the last 3 lines read:

SELECT * FROM #category;

SELECT * FROM #menu;

SELECT * FROM #coupon;>>

If these really are the last three lines of SP1, then what you're trying to do in SP2 has already been done - to create a local temporary table called #menu, and populate it. Am I missing something here? It's sunny in London today and, well, it's spring too...

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kit G SSCrazy Points: 2888 More actions · Answer 9

There are times when the best solution is to have one stored procedure call data from another stored procedure. I have a procedure that calls the information needed for doing a presort of addresses that from that information has to decide which way to presort. The first procedure gathers the data and does whatever other manipulation is needed (marking single records not be used from the whole dataset) and then calls the correct stored procedure depending on how the data is to be presorted (First Class or Standard Mail). Otherwise I would have to duplicate the first procedure several times to match the ways I can presort.

The second procedure does call the data from the temporary table, however, not from a select. The second stored procedure needs to have the call to the temp table in it, not the first procedure doing the select. That might help you out.

The DDL and DML of your procedures would help a lot more as without them, we can only give suggestions that may or may not work cause we don't know what else is going on.

-- Kit