Sql-server – INSERT EXEC statement cannot be nested

sql serverstored-procedures

I have a stored proc spGetSites that returns three columns for SiteName, SiteId and UnitCount. The stored proc runs perfectly fine.
I'm trying to store the results of the stored proc to a temp table @Site to use for my report with below syntax. This time I got an error message: "INSERT EXEC statement cannot be nested"

Can I not use INSERT and EXEC statement at the same time?

DECLARE @Site TABLE (SiteName VARCHAR(100), SiteId INT, UnitCount INT)

INSERT INTO @Site
EXEC spGetSites @SiteId = 0

Best Answer

If you look at the code for your spGetSites procedure, somewhere in that procedure is another INSERT...EXEC. It may be directly in that procedure, or nested in the bowels of some other procedure it calls.

Ultimately, if a stored procedure uses INSERT...EXEC then if you try to call that stored procedure in the context of another INSERT...EXEC you will get the error you are seeing.

How do you fix it?

You could simply take the inner INSERT...EXEC and inline the code to this single stored procedure. Though, I suspect that other procedure may be there for a reason: ie to keep your code DRY.
Since this is a Get procedure, hopefully there's no data manipulation happening anywhere in the call stack. You could convert the child procedure into a table-valued function. This would allow you to convert that inner INSERT...EXEC into an INSERT...SELECT and resolve this issue.
Use temp tables scoped to the outer procedure to pass data between procedures. This solution gets complicated, so it's not my favorite, and I generally discourage this pattern when there's a better option--but for the sake of completeness, I'll include it here. Basically, if you create your #temp table outside spGetSites you can use it inside spGetSites (without creating it inside there), and the table with data inserted will survive the procedure execution and continue to work.

I don't like option 3 because it's a coding pattern complex enough to ensure someone messes it up in the future, unless everyone is on board and familiar with the coding pattern: * spGetSites will fail unless you create the table first. All callers need to remember to create the table exactly the same first. * spGetSites can't assume the table is empty. It might have existing data from the outer call (or a prior execution from the same caller) * Troubleshooting and debugging (and even getting a query plan) for spGetSites is more complex because of the table creation confusion.

What would I do?

Without knowing how complex the code is behind spGetSites, I'd look into creating an inline TVF that replaces the inner INSERT...EXEC with INSERT...SELECT or possibly all of spGetSites could be simplified/rewritten to make it self contained without the INSERT...EXEC

Related Solutions

Sql-server – SQL Server 2008 R2 MERGE statement to replace single INSERT AND UPDATE statement combined

I haven't done any comparative testing of the two (yet) nor seen any articles on the topic. There is an Optimizing MERGE Statement Performance article on Technet but this doesn't include any comparisons with the update/insert syntax.

I can however suggest an improvement over your original syntax which eliminates the IF EXISTS lookup:

UPDATE 
    dbo.tblCustomer
SET 
    CustomerName = @CustomerName
WHERE
    CustomerID = @CustomerID;

IF (@@ROWCOUNT = 1)
BEGIN
    SELECT @New_ID = @CustomerID;
END
ELSE
BEGIN
    INSERT 
        dbo.tblCustomer
        (Taalnaam)
    VALUES
        (@CustomerName);

    SELECT @New_ID = SCOPE_IDENTITY();
END

You may also be interested in Mythbusting: Concurrent Update/Insert Solutions, which includes some examples of MERGE usage.

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );