Handling exceptions in stored procedures called using insert-exec blocks

Question

I have a stored procedure that is called in an insert-exec block:

insert into @t
    exec('test')

How can I handle exceptions generated in the stored procedure and still continue processing?

The following code illustrates the problem. What I want to do is return 0 or -1 depending on the success or failure of the internal exec() call:

alter procedure test -- or create
as
begin try
    declare @retval int;
    -- This code assumes that PrintMax exists already so this generates an error
    exec('create procedure PrintMax as begin print ''hello world'' end;')
    set @retval = 0;
    select @retval;
    return(@retval);
end try
begin catch
    -- if @@TRANCOUNT > 0 commit;
    print ERROR_MESSAGE();
    set @retval = -1;
    select @retval;
    return(@retval);
end catch;
go

declare @t table (i int);

insert into @t
    exec('test');

select *
from @t;

My problem is the return(-1). The success path is fine.

If I leave out the try/catch block in the stored procedure, then the error is raised and the insert fails. However, what I want to do is to handle the error and return a nice value.

The code as is returns the message:

Msg 3930, Level 16, State 1, Line 6
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

This is perhaps the worst error message I've encountered. It seems to really mean "You did not handle an error in a nested transaction."

If I put in the if @@TRANCOUNT > 0, then I get the message:

Msg 3916, Level 16, State 0, Procedure gordontest, Line 7
Cannot use the COMMIT statement within an INSERT-EXEC statement unless BEGIN TRANSACTION is used first.

I've tried playing around with begin/commit transaction statements, but nothing seems to work.

So, how can I have my stored procedure handle errors without aborting the overall transaction?

Edit in response to Martin:

The actual calling code is:

        declare @RetvalTable table (retval int);

        set @retval = -1;

        insert into @RetvalTable
            exec('

declare @retval int; exec @retval = '+@query+'; select @retval' );

        select @retval = retval from @RetvalTable;

Where @query is the stored procedure call. The goal is to get the return value from the stored procedure. If this is possible without an insert (or, more specifically, without starting a transaction), that would be great.

I cannot modify the stored procedures in general to store the value in a table, because there are too many of them. One of them is failing, and I can modify that. My current best solution is something like:

if (@StoredProcedure = 'sp_rep__post') -- causing me a problem
begin
    exec @retval = sp_rep__post;
end;
else
begin
    -- the code I'm using now
end;

Answer 1

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 );