Obsługa wyjątków w procedurach przechowywanych wywoływanych za pomocą bloków insert-exec

Mam procedurę składowaną, która jest wywoływana w bloku insert-exec:

insert into @t
    exec('test')

Jak mogę obsłużyć wyjątki wygenerowane w procedurze przechowywanej i nadal kontynuować przetwarzanie?

Poniższy kod ilustruje problem. To, co chcę zrobić, to zwrócić 0 lub -1 w zależności od powodzenia lub niepowodzenia exec()połączenia wewnętrznego :

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;

Moim problemem jest return(-1). Ścieżka sukcesu jest w porządku.

Jeśli pominę blok try / catch w procedurze przechowywanej, błąd zostanie zgłoszony, a wstawianie nie powiedzie się. Chcę jednak poradzić sobie z błędem i zwrócić niezłą wartość.

Kod w postaci, w jakiej jest, zwraca komunikat:

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.

To chyba najgorszy komunikat o błędzie, jaki napotkałem. Wydaje się, że naprawdę oznacza „Nie obsłużyłeś błędu w transakcji zagnieżdżonej”.

Jeśli wstawię if @@TRANCOUNT > 0, otrzymuję komunikat:

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.

Próbowałem bawić się przy użyciu instrukcji rozpoczęcia / zatwierdzenia transakcji, ale wydaje się, że nic nie działa.

Jak więc mogę, aby moja procedura przechowywana obsługiwała błędy bez przerywania całej transakcji?

Edytuj w odpowiedzi na Martin:

Rzeczywisty kod wywoławczy to:

        declare @RetvalTable table (retval int);

        set @retval = -1;

        insert into @RetvalTable
            exec('

deklarować @retval int; exec @retval = '+ @ query +'; wybierz @retval ');

        select @retval = retval from @RetvalTable;

Gdzie @queryjest wywołanie procedury składowanej. Celem jest uzyskanie wartości zwracanej z procedury składowanej. Jeśli jest to możliwe bez insert(a dokładniej bez rozpoczynania transakcji), byłoby świetnie.

Nie mogę ogólnie modyfikować procedur przechowywanych w celu przechowywania wartości w tabeli, ponieważ jest ich zbyt wiele. Jedna z nich zawodzi i mogę to zmienić. Moje obecne najlepsze rozwiązanie to:

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;

Błąd w EXECczęści INSERT-EXECwyciągu powoduje pozostawienie transakcji w stanie skazanym na niepowodzenie.

Jeśli PRINTwychodzisz XACT_STATE()z CATCHbloku, jest ustawiony na -1.

Nie wszystkie błędy spowodują ustawienie tego stanu. Następujący błąd ograniczenia sprawdzania przechodzi do bloku catch i INSERTkończy się powodzeniem.

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; 

Dodanie tego do CATCHbloku

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Nie pomaga Daje błąd

Nie można użyć instrukcji ROLLBACK w instrukcji INSERT-EXEC.

Nie sądzę, aby istniał jakikolwiek sposób na odzyskanie takiego błędu po jego wystąpieniu. W konkretnym przypadku użytkowania i tak nie potrzebujesz INSERT ... EXEC. Możesz przypisać wartość zwracaną do zmiennej skalarnej, a następnie wstawić ją do oddzielnej instrukcji.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC) 

Lub oczywiście możesz zrestrukturyzować wywoływaną procedurę przechowywaną, aby w ogóle nie zgłaszała tego błędu.

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