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
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 = retval from @RetvalTable;
@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;