SQL Server Performance

An INSERT EXEC statement cannot be nested.

Discussion in 'SQL Server 2005 General Developer Questions' started by ankitmathur, Oct 29, 2009.

  1. ankitmathur New Member

    Hi All,

    While trying to develop an online reporting panel I'm stuck on a problem I'm unable to find an answer on my own.

    Hence, the request for you all to help me.

    One of the reports require me to store output of an existing SP and make some calculations on it.

    Just to make it easy to understand I'm using some naming here so that I can explain it easily.

    Top Level (Problem) SP-> SP3
    Nested SP-> SP2
    Another nested SP within SP2-> SP1

    When I try to run SP3 I get this error message "An INSERT EXEC statement cannot be nested."
    Reason: As much as I could debug this issue is coming in SP2 at a statement where SP2 calls and stores the output of SP1 in a temp table.

    While SP2 runs smoothly on a standalone basis when its output is called in SP3 I get an error.

    If this particular SP2 statement is commented and values hard-coded i.e. SP1 is never called, SP3 also runs fine.

    So as the error also says it has something to do with how much deep nested SPs can work?

    Further, I've tried using TEMP Table, Temporary table variable and defined a proper table to store these nested SPs output thinking just in case TEMP tables have this limitation but nothing worked.

    Kindly, suggest me how do I get SP3 working with just 2 level deep nested SPs being used.

    Ankit Mathur
  2. FrankKalis Moderator

    Have you found a solution or workaround in the meantime?
  3. Adriaan New Member

    I remember this being an issue with certain system sprocs where you can't insert the results into a temp table because the underlying sproc is inserting into a temp table as well.
    AFAIK, the only possible work-around is to code everything in the main procedure.
  4. ankitmathur New Member

    Well as of now I'm trying to create a UDF doing exactly what one of the sub-procs are doing to avoid one level of nesting.
    I'm hoping against the hope that it won't throw up this or, some other error when a select * from function_name would be given in one level of sub-proc.
    Last option of course would be to bring everything under one umbrella. But what a waste that would be esp. considering the fact that I consolidated other SPs so that this will work.
    Will update about the course of action finally taken. In the meanwhile if any of experts gets an idea I'll be delighted to hear from you all.
    Ankit Mathur

Share This Page