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;

• Martin Smith: What are you trying to insert into the table variable? The return value doesn't get inserted there anyway. declare @t table (i int);declare @RC int;exec @RC = test;insert into @t values (@RC);select * from @t; works fine.
• Gordon Linoff: @MartinSmith . . . The way the code really works is more like select @retval; return @retval at the end. If you know another way to get the return value from a dynamic stored procedure call, I would love to know.
• Martin Smith: Well another way would be DECLARE @RC INT;EXEC sp_executesql N'EXEC @RC = test', N'@RC INT OUTPUT', @RC = @RC OUTPUT;insert into @t VALUES (@RC)
• Gordon Linoff: @MartinSmith . . . I think that will work. We spent half the day looking for hardware fault ("cannot support operations that write to the log file" sounds like a hardware failure) and the past couple of hours trying to get the code right. Variable substitution is an excellent answer.

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