An INSERT EXEC statement cannot be nested

  • CREATE PROC GetData

    As

    DECLARE @STR NVARCHAR(MAX)

    SET @STR = N'SELECT 1,''XYZ''';

    DECLARE @tempTab TABLE

    (

    ID INT,

    [NAME] VARCHAR(100)

    )

    INSERT INTO @tempTab

    EXEC SP_EXECUTESQL @STR

    SELECT ID,[NAME] FROM @tempTab

    GO

    CREATE PROC FetchData

    As

    DECLARE @tempTab TABLE

    (

    ID INT,

    [NAME] VARCHAR(100)

    )

    INSERT INTO @tempTab

    EXEC GetData

    SELECT * from @tempTab

    GO

    EXEC FetchData

    When I try to execute the SP FetchData I get the error message as follows:

    Msg 8164, Level 16, State 1, Procedure GetData, Line 12

    An INSERT EXEC statement cannot be nested.

    Any solution to this problem.

  • Why dont you wrap it as single procedure?


    Madhivanan

    Failing to plan is Planning to fail

  • No, It can not be done. The SP GetData is a generic one and can be used by multiple SP's.It is also necessary to include the dynamic SQL query in it.

    So the output of SP GetData is used by other SP's and this output is used to join with other tables to get the required data.

    The problem occurs when we execute a dynamic SQL query inside a SP and call this SP in another one to insert data in a temporary table.

  • SP_EXECUTESQL executes in a different session and not in the session where it is called. i.e. if proc xyz contains a SP_EXECUTESQL statement then this is not executed in the proc xyz's session.

    "Keep Trying"

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply