Trapping results of a stored procedure called inside a procedure

[9654]

Inside a stored procedure i am calling another stored procedure using exec and that stored procedure creates a temp table.
But when i try to access that temp table from the main procedure it says invalid object.
Is there any way to access that temp table.

Or what would be the alternative method.

 

[mrdenny]

You would need to create the temp table as a global temp table. Temp tables aren't available to the parent of a stored procedure. Temp tables are destroyed when the calling code is completed (in this case the child procedure is the calling code). A global temp table should remain available until the parent code completes.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Catadmin]

The only coding difference between the two is that a global temp table uses ## instead of # (which is what a local table uses).

So you won't have to change your code a whole heck of a lot. @=)

Just make sure to drop the table when you're done with it, or it will persist until all users/procedures accessing the table have logged off, which can be nasty.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[gmmastros]

There is another way if you are returning a table from the inner stored procedure.

To test this, first create this stored procedure. When you run it, you'll see that a recordset with 5 records are returned.

ALTER Procedure TestReturnTable
	@SomeValue VarChar(20)
As
Declare @TempResult
Table	(RowId Integer Identity(1,1), Value VarChar(20))

Insert Into @TempResult(Value) Values(@SomeValue)
Insert Into @TempResult(Value) Values(@SomeValue)
Insert Into @TempResult(Value) Values(@SomeValue)
Insert Into @TempResult(Value) Values(@SomeValue)
Insert Into @TempResult(Value) Values(@SomeValue)

Select * From @TempResult

Now, create this stored procedure.

Alter Procedure TestOutterProc
AS

Create Table #TestResult(RowId Integer, Value VarChar(20))  

Declare @SQL VarChar(8000)  
Select 	@SQL = 'TestReturnTable ''This is a test'''

Insert  
Into	#TestResult
exec	(@SQL)  

Select * From #TestResult

The key to this is the Insert Exec. The results of the dynamic SQL are inserted in to the #TestResult temp table.

You can test the insert exec by running the TestOutterProc stored procedure. There are problems with this method. I think SQL Server get mad when you try to nest Insert Exec, and it doesn't work with table variables (only temp tables).

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom