15

Possible Duplicate:
How to SELECT * INTO [temp table] FROM [stored procedure]

I have a nested stored procedure call

In one of the stored procedures I want to save the result into a table variable likt this : 

INSERT INTO @myTable 
EXEC sp_myStoredProcedure

however, because the proc. is nested the following error occurs : An INSERT EXEC statement cannot be nested

The procedure must be called from another procedure, changing this is not an option. I wanted to try to use an output parameter but it still has to be set with a Insert into statement.

What are other options to save the data that is retrieved from the call of a Stored Procedure into a variable ?

Community
  • 1
  • 1
Jan
  • 9,858
  • 7
  • 26
  • 33
  • What if you call the stored procedure using `opendatasource`? Often gets around a lot of these types of limitations at the expense of opening up another connection to the SQL Server. – Martin Smith Jan 04 '11 at 16:27
  • Please do one thing ,call sp using EXECUTE sp_executesql and set one output parameter . refer link : https://support.microsoft.com/en-in/kb/262499 – Ronak Patel Oct 26 '16 at 13:05

2 Answers2

3

Table variables are not visible to the calling procedure in the case of nested procs. The following is legal with #temp tables.

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

http://support.microsoft.com/kb/305977/en-us

Jahan Zinedine
  • 14,616
  • 5
  • 46
  • 70
-3

Not tested but maybe do this:

DECLARE @insertedProc as varchar(300)
SET @insertedProc = 'INSERT INTO ' + @myTable + ' sp_myStoredProcedure'

EXEC @insertedProc

Make sure you have defined what @myTable is before hand.

wergeld
  • 14,332
  • 8
  • 51
  • 81
  • 4
    This will not work, Jan said "I want to save the result into a **table variable** " not a table specified in a variable. – Tony Jan 04 '11 at 15:58