Inserting into temp table from another SP

I have the following scenario.

SP1 - uses linked server and openquery to access data from our AS400.  I'm using several selections and putting results into temp tables, combining temp tables and Outputting results at the end of the SP to display on my ASP.Net Page.  This is working great.

I now want to reuse the code SP2.  Basically I want to call SP1 from SP2 and get the results into a temp table that I can process.  So this is the code I'm using...
INSERT INTO #tmpRecords

I am getting the following error...
An INSERT EXEC statement cannot be nested

I see this is an error I can't do much about, but is there some sort of a workaround for me so I do not have to have my logic inside of SP2 as well?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bhavesh ShahLead AnalysistCommented:

is it possible for you to share procedure?

2ndly option which coming my mind right now is.....

you can use Global temporary table.

so you can put insert statement in ## table from SP1 and directly get resultset in table for further process.

- Bhavesh
You can also turn your SP1 into a Table Function. Then the code should be something like this:

INSERT INTO #tmpRecords
Select * From dbo.YourFunction

Open in new window

Fraser_AdminAuthor Commented:
i cannot use global temp table.  several users could be accessing this at the same time.

please take a look at that :

CREATE procedure Pro1
	@param1 INT,
	@param2 INT
	SELECT @param1 AS Param1, @param2 AS Param2

CREATE procedure Pro2
	@param1 INT,
	@param2 INT

	IF OBJECT_ID('tempdb..#tmpTable','U') IS NOT NULL DROP TABLE #tmpTable
	CREATE TABLE #tmpTable (Param1 INT, Param2 INT)
	INSERT INTO #tmpTable(Param1,Param2) VALUES (1,2)
	INSERT INTO #tmpTable(Param1,Param2) VALUES (3,4)
	INSERT #tmpTable(Param1, Param2)
		EXEC Pro1 @param1,@param2

	SELECT * FROM #tmpTable

Open in new window

after that try to execute procedure2 (pro2) which uses procedure1 (pro1)

EXEC      Pro2
            @param1 = 5,
            @param2 = 6

Scott PletcherSenior DBACommented:
Yes, if you create the temp table in the calling proc, the called proc can use it.

So, add the code to caller to create the temp table.  Add a (bit) parameter to "tell" the called proc to not drop or recreate the temp table; make it optional, defaulted to 0 so that existing calls of the stored proc are not affected.

For example:

IF OBJECT_ID('tempdb.dbo.#shared_temp_table') IS NOT NULL
    DROP TABLE #shared_temp_table
CREATE TABLE #shared_temp_table (
EXEC dbo.called_proc @..., @temp_table_already_exists = 1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.