accessing rowset after exec()

Question

Post reply

accessing rowset after exec()

stevek-897782

Grasshopper

Points: 22
More actions
October 1, 2008 at 3:14 pm

#382924

IN SQL 2000 I am calling a stored procedure from within another SP using exec(). In the calling SP - How can I access the rows returned by the SQL in the called SP?

Viewing 5 posts - 1 through 5 (of 5 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

You would have to insert the output of the inner SP into a temp table, and then access the temp table.

Something like:

create proc myoutperproc()

as

begin

.....

INSERT #MyTempTable

Exec MyInnerProc

Select * from #MyTempTable

........

END

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 2

I have used the Insert Into with Exec (below) and recently ran into a Sql issue. Insert Into with Exec() does not allow nested uses.

create table #tYada (...)

Insert into #tYada

Exec usp_Yada

A different approach (not as friendly in my opinion)

Create table #tYada

Exec usp_yada (this sproc uses the #tYada created in the calling session/sproc)

I have not had any issue with this approach but the mutual dependency between the calling and called procedure makes me nervous.

daryl

"processes do not fix stupid!"

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Yes - you have to create the temp table first. SELECT...INTO doesn't work with an EXEC call. INSERT INTO does not create the table.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

stevek-897782 Grasshopper Points: 22 More actions · Answer 4

Thanks, this seems to work well, although it's probably not the most efficient process. I tried to use a Table Variable rather than a temp table, but SQL does not allow this:

INSERT @table EXEC sp_someProcedure

so #temptable works fine.

Thanks again