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;
Best Answer
The error in the
EXEC
part of theINSERT-EXEC
statement is leaving your transaction in a doomed state.If you
PRINT
outXACT_STATE()
in theCATCH
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.Adding this to the
CATCH
blockDoesn't help. It gives the error
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.Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.