2

I have three Procedures MainProcedure,Procedure1,Procedure2

1) In Procedure1 I just have a select statement ,

2) In Procedure2 am calling the Procedure1 and inserting the Output to a #table

3) In the main Procedure I am calling the Procedure2 and iam trying to insert the Output to a #table which throws an error

Msg 8164, Level 16, State 1, Procedure Procedure2, Line 10 An INSERT EXEC statement cannot be nested.

I can resolve this using Openrowset where I need to use specify Server Name ,is there any other way to solve this by not specifying the servername details

please find the sample procedure for reference

    Create Proc Procedure1
    As
    Begin
    Select 'Arun' Name, 'Pollachi' Place
    Union
    Select 'Vedaraj' Name, 'Devakottai' Place
    End
    Go



    Create Proc Procedure2
    As
    Begin
    Create Table #Table1
    (
    Name Varchar(50), Place Varchar(50)
    )
    INSERT #Table1
    Exec Procedure1
    SELECT 'Procedure2' [Source], * FROM #Table1

    DROP TABLE #Table1
    End
    Go


    Create Proc MainProcedure
    As
    Begin
    Create Table #Table1
    (
    [Source] Varchar(50), Name Varchar(50), Place Varchar(50)
    )

    INSERT #Table1
    Exec Procedure2

    select * from #Table1

    DROP TABLE #Table1
    End
    Go

can any one change my main procedure and make it to get executed Thanks!!

Arun Kumar
  • 337
  • 3
  • 7
  • 20
  • rather than INSERT #Table1 Exec Procedure1 Use INSERT INTO #Table1 Exec Procedure1 in Procedure2 – Rohan May 22 '13 at 10:02
  • @R.S INSERT #Table1 Exec Procedure1 doesnt works.. – Arun Kumar May 22 '13 at 10:07
  • Use INSERT INTO #Table1 Exec Procedure1 ...or Have a look at this thread .http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/e66c9e71-4424-4cf3-920c-6725ffc40162/ – Rohan May 22 '13 at 10:08
  • Thanks @R.S that link only gave me the solution of using open Rowset where i need to specify Server which i dont want to use – Arun Kumar May 22 '13 at 10:14
  • can any one change my main procedure and make it to get executed – Arun Kumar May 22 '13 at 11:25
  • I am also getting the above error when I run below query. Did any one suggest us whether it is possible to put stored proc result in a temp table like below. CREATE TABLE #NewGlobalTempTable( ColumnName nvarchar(150), columnValue nvarchar(150)) INSERT INTO #NewGlobalTempTable exec SearchAllTables '@tradeonemktg.com'; select * from #NewGlobalTempTable; – karunakar bhogyari Jun 19 '18 at 12:39

1 Answers1

0

Like you said, openrowset will work, but other than that the only ways I can think of would be:

  1. Change both proc 1 and proc 2 to table based functions
  2. Change proc 2 to a CLR and put all logic in there
  3. Pass the tables around as table valued parameters

There's more info about the reasoning for this here:

https://connect.microsoft.com/SQLServer/feedback/details/294571/improve-insert-exec http://dataeducation.com/revisiting-isnull-coalesce-and-the-perils-of-micro-optimization/

Community
  • 1
  • 1
Dave Hilditch
  • 5,299
  • 4
  • 27
  • 35
  • First link is now dead. Shame - finding it hard to understand why it's such a big drama with microsoft to return resultsets from one proc to another. I don't even have rollback in my stored proc and I get this error. – youcantryreachingme Mar 25 '22 at 04:13
  • IIRC, it was related to caching - caching execution plans, caching results. – Dave Hilditch Mar 29 '22 at 13:15