Insert second result set from stored procedure into table

Question

Insert second result set from stored procedure into table

jondy87

SSC-Addicted

Points: 426
More actions
October 6, 2011 at 12:18 pm

#260117

hi All,
I have a stored procedure that returns two result sets which needs to be
inserted to two physical tables
Is there a way to do this using SQL statements
Thank you

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

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

bkubicek SSChampion Points: 10948 More actions · Answer 1

jondy87 (10/6/2011)
hi All,
I have a stored procedure that returns two result sets which needs to be
inserted to two physical tables
Is there a way to do this using SQL statements
Thank you

Is there a reason why you don't have the stored proc insert the results into two physical tables?

jondy87 SSC-Addicted Points: 426 More actions · Answer 2

Am trying to capture the result sets from stored procedures that are being used by the front end

All i can do is run the SP and store the result to my local database

bkubicek SSChampion Points: 10948 More actions · Answer 3

jondy87 (10/6/2011)
Am trying to capture the result sets from stored procedures that are being used by the front end
All i can do is run the SP and store the result to my local database

So three is no option to add a new optional parameter that will only return one or the other result set? Or a optional parameter that writes those values to tables?

I am not familiar with anyway to process multiple result sets in sql.

Jared SSC Guru Points: 61793 More actions · Answer 4

It would help if we could see the stored procedure.

Thanks,

Jared

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 5

However, the "simplest" way with your current SP is to put the 2 sets into temp tables, then insert the data from the 2 temp tables into your 2 physical tables, then select from the 2 temp tables. This way, you know you are inserting only what you are returning. Unless of course you can insert some unique identifier with each "set" of data that can then be used in the filter condition of the SELECT. That is a bit trickier though, at least from my point of view.

Thanks,

Jared

Jared
CE - Microsoft

tsandeep1407 SSC Veteran Points: 293 More actions · Answer 6

use dbname

go

drop procedure dbo.p_inner

go

create procedure dbo.p_inner

as

select 1,NULL,NULL

select 2,3,4

go

drop procedure dbo.p_outer

go

create procedure dbo.p_outer

as

declare @t table (id int, c1 int, c2 int)

insert @t (id, c1,c2)

exec dbo.p_inner

select * from @t

go

exec dbo.p_inner

exec dbo.p_outer

I have dbo.p_inner proc gives 2 resultsets, i have dbo.p_outer proc written to execute inner proc which gives me union of dbo.p_inner resultset.....HOW????????

Results:

(No column name)(No column name)(No column name)

1NULLNULL

(No column name)(No column name)(No column name)

234

idc1c2

1NULLNULL

234

Someone please tellme.....dont tell me both has same number of columns in o/p.

Jared SSC Guru Points: 61793 More actions · Answer 7

create procedure dbo.p_inner

as

select 1,NULL,NULL

UNION ALL

select 2,3,4

go

Without the union in the proc, it will create 2 result sets because it IS doing 2 selects. a stored proc will return as many result sets as there are queries. It is simply a saved batch of queries.

Jared
CE - Microsoft

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Rumor has it that this is what "MARS" is for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)