Pourquoi Sql Server garde après l'exécution de l'instruction raiserror quand xact_abort est sur?

Question

Je viens de recevoir surpris par quelque chose en TSQL. J'ai pensé que si xact_abort était sur, appeler quelque chose comme

raiserror('Something bad happened', 16, 1);

permettrait d'arrêter l'exécution de la procédure stockée (ou un lot).

Mais mon ADO.NET message d'erreur juste prouvé le contraire. Je l'ai eu à la fois l'instruction raiserror message d'erreur dans le message d'exception, en plus, la prochaine chose qui a éclaté après que.

C'est ma solution (qui est mon habitude de toute façon), mais il ne semble pas comme il devrait être nécessaire:

if @somethingBadHappened
    begin;
        raiserror('Something bad happened', 16, 1);
        return;
    end;

Les docs disent ceci:

Lorsque SET XACT_ABORT est SUR, si une instruction Transact-SQL soulève une erreur d'exécution, la totalité de la transaction est terminée et restaurée.

Est-ce à dire que je doit utiliser une transaction explicite?

Answer 1

C'est Par la Conception^TM, comme vous pouvez le voir sur se Connecter par l'équipe SQL Server réponse à une question similaire:

Merci pour vos commentaires. De par sa conception, le set XACT_ABORT option n'a pas d'impact sur le comportement de l'instruction RAISERROR. On tiendra compte de vos commentaires pour modifier ce comportement pour une future version de SQL Server.

Oui, c'est un peu un problème pour certains qui espéraient RAISERROR avec une forte gravité (comme 16) serait le même que l'exécution de SQL erreur - il n'est pas.

Votre solution de contournement est à peu près ce que vous devez faire, et à l'aide d'une transaction explicite n'a aucun effet sur le comportement que vous souhaitez modifier.

Answer 2

Si vous utilisez un bloc try/catch une instruction raiserror numéro d'erreur avec la gravité 11-19 va entraîner l'exécution de sauter le bloc catch.

Toute la gravité au-dessus de 16 est une erreur de système. Pour démontrer le code suivant définit un bloc try/catch et exécute une procédure stockée que nous supposons ne fonctionne pas:

supposons que nous avons une table [dbo].[Erreurs] pour contenir des erreurs supposons que nous avons une procédure stockée [dbo].[AssumeThisFails] qui va échouer lorsque nous l'exécuter

-- first lets build a temporary table to hold errors
if (object_id('tempdb..#RAISERRORS') is null)
 create table #RAISERRORS (ErrorNumber int, ErrorMessage varchar(400), ErrorSeverity int, ErrorState int, ErrorLine int, ErrorProcedure varchar(128));

-- this will determine if the transaction level of the query to programatically determine if we need to begin a new transaction or create a save point to rollback to
declare @tc as int;
set @tc = @@trancount;
if (@tc = 0)
 begin transaction;
else
 save transaction myTransaction;

-- the code in the try block will be executed
begin try
 declare @return_value = '0';
 set @return_value = '0';
 declare
  @ErrorNumber as int,
  @ErrorMessage as varchar(400),
  @ErrorSeverity as int,
  @ErrorState as int,
  @ErrorLine as int,
  @ErrorProcedure as varchar(128);


 -- assume that this procedure fails...
 exec @return_value = [dbo].[AssumeThisFails]
 if (@return_value <> 0)
  raiserror('This is my error message', 17, 1);

 -- the error severity of 17 will be considered a system error execution of this query will skip the following statements and resume at the begin catch block
 if (@tc = 0)
  commit transaction;
 return(0);
end try


-- the code in the catch block will be executed on raiserror("message", 17, 1)
begin catch
  select
   @ErrorNumber = ERROR_NUMBER(),
   @ErrorMessage = ERROR_MESSAGE(),
   @ErrorSeverity = ERROR_SEVERITY(),
   @ErrorState = ERROR_STATE(),
   @ErrorLine = ERROR_LINE(),
   @ErrorProcedure = ERROR_PROCEDURE();

  insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
   values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);

  -- if i started the transaction
  if (@tc = 0)
  begin
   if (XACT_STATE() <> 0)
   begin
     select * from #RAISERRORS;
    rollback transaction;
    insert into [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
     select * from #RAISERRORS;
    insert [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
     values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
    return(1);
   end
  end
  -- if i didn't start the transaction
  if (XACT_STATE() = 1)
  begin
   rollback transaction myTransaction;
   if (object_id('tempdb..#RAISERRORS') is not null)
    insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
     values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
   else
    raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
   return(2); 
  end
  else if (XACT_STATE() = -1)
  begin
   rollback transaction;
   if (object_id('tempdb..#RAISERRORS') is not null)
    insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
     values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
   else
    raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
   return(3);
  end
 end catch
end

Answer 3

Utiliser RETURN immédiatement après l' RAISERROR() et ça va pas exécuter la procédure plus loin.

Answer 4

Comme l'a souligné sur MSDN l' THROW déclaration doit être utilisé à la place de RAISERROR.

Les deux se comportent un peu différemment. Mais lorsqu' XACT_ABORT est définie SUR en fonction, alors vous devriez toujours utiliser l' THROW commande.