CODEWITHSUNDEEP
sql-serverstored-procedures
jf328
jf328

Reputation: 7351

SQL Server, passing stored procedure results by using temp table

I'm writing a stored procedure which uses the result of another stored procedure by using temp table. The inner stored procedure usp_i is in database A and the outer stored procedure usp_o is in database B. All databases are on the same server.

At the end of usp_i, I put its results into the #tmpResult table:

use A
go

select * into #tmpResult from SomeTable

Now in the caller usp_o, I have

use B
go

exec A.dbo.usp_i
select * from #tmpResult
select * from tmpdb..#tmpResult

Both ways I got an error

Invalid object name #tmpResult

How can I get the temp table across?

Upvotes: 1

Views: 186

Answers (3)

Squirrel
Squirrel

Reputation: 24813

pre create the temp table in usp_o, and in usp_i, insert rows and you will be able to access in usp_o

-- in usp_o
create table #tmpResult ( ... )

exec A.dbo.usp_i



-- in usp_i
insert into #tmpResult (  ... ) select ... from sometable

Upvotes: 1

Hong Van Vit
Hong Van Vit

Reputation: 2986

I think you can use table ##:

select * into ##tmpResult from SomeTable

Upvotes: 0

Chris Mack
Chris Mack

Reputation: 5208

Create the temp table in your outer proc and INSERT INTO it in your inner proc.

Upvotes: 1

Related Questions