<p>I just got surprised by something in TSQL.  I thought that if xact_abort was on, calling something like</p>

<pre><code>raiserror('Something bad happened', 16, 1);
</code></pre>

<p>would stop execution of the stored procedure (or any batch).</p>

<p>But my ADO.NET error message just proved the opposite.  I got both the raiserror error message in the exception message, plus the next thing that broke after that.</p>

<p>This is my workaround (which is my habit anyway), but it doesn't seem like it should be necessary:</p>

<pre><code>if @somethingBadHappened
    begin;
        raiserror('Something bad happened', 16, 1);
        return;
    end;
</code></pre>

<p>The docs say this:</p>

<blockquote>
  <p>When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.</p>
</blockquote>

<p>Does that mean I must be using an explicit transaction?</p>

[{'answer_id': 76416, 'author': 'Philip Rieck', 'author_id': 12643, 'author_profile': 'https://Stackoverflow.com/users/12643', 'pm_score': 6, 'selected': False, 'text': '<p>This is By Design<sup>TM</sup>, as you can see on <a href="http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=275308" rel="noreferrer">Connect</a> by the SQL Server team\'s response to a similar question:</p>\n\n<blockquote>\n  <p>Thank you for your feedback. By design, the XACT_ABORT set option does not impact the behavior of the RAISERROR statement. We will consider your feedback to modify this behavior for a future release of SQL Server. </p>\n</blockquote>\n\n<p>Yes, this is a bit of an issue for some who hoped <code>RAISERROR</code> with a high severity (like <code>16</code>) would be the same as an SQL execution error - it\'s not.</p>\n\n<p>Your workaround is just about what you need to do, and using an explicit transaction doesn\'t have any effect on the behavior you want to change.</p>\n'}, {'answer_id': 77140, 'author': 'ninegrid', 'author_id': 13661, 'author_profile': 'https://Stackoverflow.com/users/13661', 'pm_score': 5, 'selected': False, 'text': '<p>If you use a try/catch block a raiserror error number with severity 11-19 will cause execution to jump to the catch block.</p>\n\n<p>Any severity above 16 is a system error.  To demonstrate the following code sets up a try/catch block and executes a stored procedure that we assume will fail:</p>\n\n<p>assume we have a table [dbo].[Errors] to hold errors\nassume we have a stored procedure [dbo].[AssumeThisFails] which will fail when we execute it</p>\n\n<pre><code>-- first lets build a temporary table to hold errors\nif (object_id(\'tempdb..#RAISERRORS\') is null)\n create table #RAISERRORS (ErrorNumber int, ErrorMessage varchar(400), ErrorSeverity int, ErrorState int, ErrorLine int, ErrorProcedure varchar(128));\n\n-- 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\ndeclare @tc as int;\nset @tc = @@trancount;\nif (@tc = 0)\n begin transaction;\nelse\n save transaction myTransaction;\n\n-- the code in the try block will be executed\nbegin try\n declare @return_value = \'0\';\n set @return_value = \'0\';\n declare\n  @ErrorNumber as int,\n  @ErrorMessage as varchar(400),\n  @ErrorSeverity as int,\n  @ErrorState as int,\n  @ErrorLine as int,\n  @ErrorProcedure as varchar(128);\n\n\n -- assume that this procedure fails...\n exec @return_value = [dbo].[AssumeThisFails]\n if (@return_value &lt;&gt; 0)\n  raiserror(\'This is my error message\', 17, 1);\n\n -- 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\n if (@tc = 0)\n  commit transaction;\n return(0);\nend try\n\n\n-- the code in the catch block will be executed on raiserror("message", 17, 1)\nbegin catch\n  select\n   @ErrorNumber = ERROR_NUMBER(),\n   @ErrorMessage = ERROR_MESSAGE(),\n   @ErrorSeverity = ERROR_SEVERITY(),\n   @ErrorState = ERROR_STATE(),\n   @ErrorLine = ERROR_LINE(),\n   @ErrorProcedure = ERROR_PROCEDURE();\n\n  insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)\n   values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);\n\n  -- if i started the transaction\n  if (@tc = 0)\n  begin\n   if (XACT_STATE() &lt;&gt; 0)\n   begin\n     select * from #RAISERRORS;\n    rollback transaction;\n    insert into [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)\n     select * from #RAISERRORS;\n    insert [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)\n     values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);\n    return(1);\n   end\n  end\n  -- if i didn\'t start the transaction\n  if (XACT_STATE() = 1)\n  begin\n   rollback transaction myTransaction;\n   if (object_id(\'tempdb..#RAISERRORS\') is not null)\n    insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)\n     values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);\n   else\n    raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);\n   return(2); \n  end\n  else if (XACT_STATE() = -1)\n  begin\n   rollback transaction;\n   if (object_id(\'tempdb..#RAISERRORS\') is not null)\n    insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)\n     values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);\n   else\n    raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);\n   return(3);\n  end\n end catch\nend\n</code></pre>\n'}, {'answer_id': 5991091, 'author': 'piyush', 'author_id': 752253, 'author_profile': 'https://Stackoverflow.com/users/752253', 'pm_score': 5, 'selected': False, 'text': "<p>Use <code>RETURN</code> immediately after <code>RAISERROR()</code> and it'll not execute the procedure further.</p>\n"}, {'answer_id': 18222673, 'author': 'Möoz', 'author_id': 1377865, 'author_profile': 'https://Stackoverflow.com/users/1377865', 'pm_score': 4, 'selected': False, 'text': '<p>As pointed out on the docs for <a href="https://learn.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql" rel="nofollow noreferrer"><code>SET XACT_ABORT</code></a>, the <code>THROW</code> statement should be used instead of <code>RAISERROR</code>.</p>\n\n<p>The two behave <a href="https://learn.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql" rel="nofollow noreferrer">slightly differently</a>. But when <code>XACT_ABORT</code> is set to ON, then you should always use the <code>THROW</code> command.</p>\n'}, {'answer_id': 68637587, 'author': 'Golden Lion', 'author_id': 4001177, 'author_profile': 'https://Stackoverflow.com/users/4001177', 'pm_score': 0, 'selected': False, 'text': "<p>microsoft suggests using throw instead of raiserror.  Use XACT_State to determine commit or rollback for the try catch block</p>\n<pre><code>set XACT_ABORT ON;\n\nBEGIN TRY\n     BEGIN TRAN;\n    \n     insert into customers values('Mark','Davis','markdavis@mail.com', '55909090');\n    insert into customer values('Zack','Roberts','zackroberts@mail.com','555919191');\n    COMMIT TRAN;\n  END TRY\n\nBEGIN CATCH\n    IF XACT_STATE()=-1\n        ROLLBACK TRAN;\n    IF XACT_STATE()=1\n       COMMIT TRAN;\n    SELECT ERROR_MESSAGE() AS error_message\nEND CATCH\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/76346', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1219/']