Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
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. –  Martin Smith Feb 6 '13 at 21:16
    
@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. –  Gordon Linoff Feb 6 '13 at 21:20
    
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) –  Martin Smith Feb 6 '13 at 21:30
    
@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. –  Gordon Linoff Feb 6 '13 at 21:36
    
@MartinSmith . . . If you care about points, you can put post your comment as an answer and I'll accept it. –  Gordon Linoff Feb 6 '13 at 21:39
add comment

1 Answer

up vote 9 down vote accepted

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 ); 
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.