Dan Guzman Blog

Denali CTP3: THROW Statement

by Dan Guzman

Not to mince words, T-SQL error handling has historically sucked. I’m excited that SQL Server “Denali” CTP3 (a.k.a. SQL11) includes a long-awaited THROW statement that I hope to see in the final release. In this post, I’ll dive into how this seemingly minor T-SQL enhancement will make it much easier for T-SQL developers to write robust and bug-free error handling code.

T-SQL Error Handling Ugliness

Unlike compiled application code that halts code execution upon an unhandled exception, a T-SQL might continue code execution afterward. T-SQL developers must include error checking/handling is to ensure code doesn’t continue down the “happy” path oblivious to an error, report the error to the caller, perform any necessary cleanup operations (typically ROLLBACK) and continue/halt execution as desired. The script below shows how one might accomplish this without structured error handling:




<tbody>
    
    <tr>
        
        <td width="638" valign="top" style="padding: 0in 5.4pt; border: 1pt solid red; width: 6.65in;"><br/>
        <div><span style="color: green; font-size: 10pt;">--Unstructured error handling example</span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">BEGIN</span><span style="font-size: 10pt;"> <span style="color: blue;">TRAN</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">SELECT</span><span style="font-size: 10pt;"> 1<span style="color: gray;">/</span>0 <span style="color: blue;">AS</span> CauseAnError <span style="color: green;">--report error caller</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">IF</span><span style="font-size: 10pt;"> <span style="color: fuchsia;">@@ERROR</span><span style="color: gray;">&lt;&gt;</span> 0 <span style="color: blue;">GOTO</span> ErrorHandler <span style="color: green;">--detect error</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">COMMIT</span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">GOTO</span><span style="font-size: 10pt;"> Done</span></div>
        <br/>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">ErrorHandler:</span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">IF</span><span style="font-size: 10pt;"> <span style="color: fuchsia;">@@TRANCOUNT</span><span style="color: gray;">&gt;</span> 0 <span style="color: blue;">ROLLBACK</span><span style="color: green;">--cleanup after error</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">RETURN</span><span style="font-size: 10pt;"> <span style="color: green;">--stop further code execution</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">Done:</span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">PRINT</span><span style="font-size: 10pt;"> <span style="color: red;">&#39;Done&#39;</span><span style="color: green;">--not executed after error</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">GO</span></div>
        </td>
    </tr>
</tbody>


This script results in the error:




<tbody>
    
    <tr>
        
        <td width="275" valign="top" style="padding: 0in 5.4pt; border: 1pt solid red; width: 206.6pt;"><br/>
        <div><span style="color: red; font-size: 9pt;">Msg 8134, Level 16, State 1, Line 3</span></div>
        <br/>
        <div><span style="color: red; font-size: 9pt;">Divide by zero error encountered.</span></div>
        </td>
    </tr>
</tbody>


Unstructured error handling like this is especially a pain for multi-statement scripts and stored procedures. One has to include repetitive “IF @@ERROR” check to detect errors after each statement and error-prone unstructured GOTO code. It’s easy to miss error checking/handling bugs in unit testing.

On a positive note, no T-SQL code is necessary to raise the error; SQL Server automatically reports errors to the calling application without any T-SQL code to do so (unless TRY/CATCH is used). This guarantees the calling application is notified of errors during execution.

Two Steps Forward, One Step Back

The introduction of structured error handling (TRY/CATCH) in SQL 2005 is a both a blessing and a curse. The good is that TRY/CATCH avoids the repetitive, error prone and ugly procedural code needed to check @@ERROR after each T-SQL statement and allows one to more easily centralize error handling. The structured error-handling paradigm in T-SQL is more aligned with most application languages.

Consider the equivalent script with TRY/CATCH:




<tbody>
    
    <tr>
        
        <td width="599" valign="top" style="padding: 0in 5.4pt; border: 1pt solid red; width: 449.6pt;"><br/>
        <div><span style="color: green; font-size: 10pt;">--Structured error handling example</span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">DECLARE</span></div>
        <br/>
        <div><span style="font-size: 10pt;">@ErrorNumber <span style="color: blue;">int</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: gray;">,</span>@ErrorMessage <span style="color: blue;">nvarchar</span><span style="color: gray;">(</span>2048<span style="color: gray;">)</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: gray;">,</span>@ErrorSeverity <span style="color: blue;">int</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: gray;">,</span>@ErrorState <span style="color: blue;">int</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: gray;">,</span>@ErrorLine <span style="color: blue;">int</span><span style="color: gray;">;</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">BEGIN</span><span style="font-size: 10pt;"> <span style="color: blue;">TRY</span><span style="color: green;">--detect errors</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: blue;">BEGIN</span> <span style="color: blue;">TRAN</span><span style="color: gray;">;</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: blue;">SELECT</span> 1<span style="color: gray;">/</span>0 <span style="color: blue;">AS</span> CauseAnError<span style="color: gray;">;</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: blue;">COMMIT</span><span style="color: gray;">;</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">END</span><span style="font-size: 10pt;"> <span style="color: blue;">TRY</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">BEGIN</span><span style="font-size: 10pt;"> <span style="color: blue;">CATCH</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: blue;">SELECT</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;">@ErrorNumber <span style="color: gray;">=</span><span style="color: fuchsia;">ERROR_NUMBER</span><span style="color: gray;">()</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: gray;">,</span>@ErrorMessage <span style="color: gray;">=</span><span style="color: fuchsia;">ERROR_MESSAGE</span><span style="color: gray;">()</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: gray;">,</span>@ErrorSeverity <span style="color: gray;">=</span> <span style="color: fuchsia;">ERROR_SEVERITY</span><span style="color: gray;">()</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: gray;">,</span>@ErrorState <span style="color: gray;">=</span><span style="color: fuchsia;">ERROR_STATE</span><span style="color: gray;">()</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: gray;">,</span>@ErrorLine <span style="color: gray;">=</span><span style="color: fuchsia;">ERROR_LINE</span><span style="color: gray;">();</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: blue;">IF</span> <span style="color: fuchsia;">@@TRANCOUNT</span><span style="color: gray;">&gt;</span> 0 <span style="color: blue;">ROLLBACK</span><span style="color: gray;">;</span> <span style="color: green;">--cleanup after error</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: blue;">RAISERROR</span><span style="color: gray;">(</span><span style="color: red;">&#39;Error %d caught at line %d: %s&#39;</span><span style="color: green;">--report error to caller</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: gray;">,</span>@ErrorSeverity</span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: gray;">,</span>@ErrorState</span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: gray;">,</span>@ErrorNumber</span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: gray;">,</span>@ErrorLine</span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: gray;">,</span>@ErrorMessage<span style="color: gray;">);</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: blue;">RETURN</span><span style="color: gray;">;</span><span style="color: green;">--stop further code execution</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">END</span><span style="font-size: 10pt;"> <span style="color: blue;">CATCH</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">PRINT</span><span style="font-size: 10pt;"> <span style="color: red;">&#39;Done&#39;</span><span style="color: gray;">;</span> <span style="color: green;">--not executed after error</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">GO</span></div>
        </td>
    </tr>
</tbody>






<tbody>
    
    <tr>
        
        <td width="638" valign="top" style="padding: 0in 5.4pt; border: 1pt solid red; width: 6.65in;"><br/>
        <div><span style="color: red; font-size: 9pt;">Msg 50000, Level 16, State 1, Line 21</span></div>
        <br/>
        <div><span style="color: red; font-size: 9pt;">Error 8134 caught at line 10: Divide by zero error encountered</span></div>
        </td>
    </tr>
</tbody>


I really like the way structured error handling catches errors declaratively with centralized error handling. But TRY/CATCH introduces a couple of issues. Foremost is reporting of the error to the caller. A caught error prevents the error message from being returned to the client. When TRY/CATCH is employed, the developer assumes responsibility to notify the application that an error occurred. Failure to do so will result in a silent error undetectable by the calling application, which is seldom desirable. Using TRY/CATCH necessitates that you write a bit of code in the CATCH block to capture, report and/or log error details as well as control code flow after the error.

Another downside of TRY/CATCH before Denali is that you cannot raise the original error because RAISERROR does not allow a system error number to be specified (8134 in this example). Consequently, the divide by zero system error here cannot be raised in the CATCH block; a user-defined error in the 50000+ error number range must be raised instead, obfuscating the original error and line number. So instead of returning error information natively, you must write code to return original error details by some other means, such as in the error message text. This often leads to inconsistencies in the way errors are reported.

THROW to the Rescue

Denali introduces a simple THROW statement. THROW in a CATCH block with no parameters raises the caught error and stops further code execution unless an outer CATCH block exists. This greatly simplifies CATCH block error reporting and control flow code since this THROW behavior is exactly what one typically does after handling a T-SQL error. Furthermore, unlike RAISERROR, THROW retains the original error number, message text, state, severity and line number. This is the biggest T-SQL error handling enhancement since the introduction of TRY/CATCH in SQL Server 2005.

The THROW example below raises the original error and stops further code execution and is less verbose and error-prone than other methods:




<tbody>
    
    <tr>
        
        <td width="581" valign="top" style="padding: 0in 5.4pt; border: 1pt solid red; width: 436.1pt;"><br/>
        <div><span style="color: green; font-size: 10pt;">--Structured error handling example in Denali CTP3</span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">BEGIN</span><span style="font-size: 10pt;"> <span style="color: blue;">TRY</span><span style="color: green;">--detect errors</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: blue;">BEGIN</span> <span style="color: blue;">TRAN</span><span style="color: gray;">;</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: blue;">SELECT</span> 1<span style="color: gray;">/</span>0 <span style="color: blue;">AS</span> CauseAnError<span style="color: gray;">;</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: blue;">COMMIT</span><span style="color: gray;">;</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">END</span><span style="font-size: 10pt;"> <span style="color: blue;">TRY</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">BEGIN</span><span style="font-size: 10pt;"> <span style="color: blue;">CATCH</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;"><span style="color: blue;">IF</span> <span style="color: fuchsia;">@@TRANCOUNT</span><span style="color: gray;">&gt;</span> 0 <span style="color: blue;">ROLLBACK</span><span style="color: gray;">;</span> <span style="color: green;">--cleanup after error</span></span></div>
        <br/>
        <div><span style="font-size: 10pt;">THROW<span style="color: gray;">;</span> <span style="color: green;">--report error to caller and stop further code execution</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">END</span><span style="font-size: 10pt;"> <span style="color: blue;">CATCH</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">PRINT</span><span style="font-size: 10pt;"> <span style="color: red;">&#39;Done&#39;</span><span style="color: gray;">;</span> <span style="color: green;">--not executed after error</span></span></div>
        <br/>
        <div><span style="color: blue; font-size: 10pt;">GO</span></div>
        </td>
    </tr>
</tbody>






<tbody>
    
    <tr>
        
        <td width="449" valign="top" style="padding: 0in 5.4pt; border: 1pt solid red; width: 337.1pt;"><br/>
        <div><span style="color: red; font-size: 9pt;">Msg 8134, Level 16, State 1, Line 4</span></div>
        <br/>
        <div><span style="color: red; font-size: 9pt;">Divide by zero error encountered.</span></div>
        </td>
    </tr>
</tbody>


There are only a couple of scenarios I can think of not to use THROW in a CATCH block. One is when you need to continue code execution in the same scope after an error. Another is in an outermost catch block when you want to prevent the error from being returned to the client. However, these cases are the exception (no pun intended) rather than the rule.

Summary

THROW is a simple, yet powerful extension to SQL Server error handling. I’ll discuss some other enhancements to the core database engine as outlined in the What’s New section of the SQL Server “Denali” Books Online in future posts as well.

Legacy Comments
Nitin
2012-01-19		 re: Denali CTP3: THROW Statement
As always, its helpful to learn from the masters