title: Weird behavior of Try-Catch in a Stored Procedure
body: <p>I have a stored procedure where I need to cast to a type, but do not know if the cast will succeed. In an imperative language, I would use some sort of <code>TryCast</code> pattern. I figured that this would be equivalent in T-SQL:</p>
  
  <pre><code>begin try
     select cast(@someValue as SomeType)
  end try begin catch end catch
  </code></pre>
  
  <p>On the surface, it does appear to be equivalent. If <code>@SomeTypeVar</code> is uninitialized and the cast fails, I get <code>NULL</code> to work with; the correct value if the cast succeeds.</p>
  
  <p>I used this same code in a stored procedure, but that yields an error: <code>Msg 2812, Level 16, State 62, Line 20. The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.</code> Some research led me to other questions on Stack Overflow and this table of times when <code>try-catch</code> fails in T-SQL:</p>
  
  <blockquote>
    <p>TRY…CATCH constructs do not trap the following conditions:</p>
    
    <ul>
    <li>Warnings or informational messages that have a severity of 10 or
    lower. </li>
    <li>Errors that have a severity of 20 or higher that stop the SQL
    Server Database Engine task processing for the session. If an error
    occurs that has severity of 20 or higher and the database connection
    is not disrupted, TRY…CATCH will handle the error. </li>
    <li>Attentions, such as
    client-interrupt requests or broken client connections. </li>
    <li>When the
    session is ended by a system administrator by using the KILL
    statement. </li>
    </ul>
    
    <p>The following types of errors are not handled by a CATCH
    block when they occur at the same level of execution as the TRY…CATCH
    construct:</p>
    
    <ul>
    <li>Compile errors, such as syntax errors, that prevent a batch from
    running. </li>
    <li>Errors that occur during statement-level recompilation, such
    as object name resolution errors that occur after compilation because
    of deferred name resolution. </li>
    </ul>
    
    <p>These errors are returned to the level
    that ran the batch, stored procedure, or trigger.</p>
  </blockquote>
  
  <p>At first, I thought I fell into the statement-level recompilation bucket (as my error level is 16) until I tried to bisect the problem. The minimal reproduction is as follows:</p>
  
  <pre><code>create procedure failsInTransactions
  as
  begin
      begin try
        select cast(@someValue as SomeType)
      end try begin catch end catch
  end
  </code></pre>
  
  <p>and the calling code:</p>
  
  <pre><code>begin tran
    exec failsInTransactions
  commit
  </code></pre>
  
  <p>This yields the error I discussed above. However, I remembered that if a stored procedure doesn't have any parameters, you can call it without exec. This:</p>
  
  <pre><code>begin tran
    failsInTransactions
  commit
  </code></pre>
  
  <p>succeeds with <code>Command(s) completed successfully.</code> Further experimentation led me to another error with level 16:</p>
  
  <pre><code>begin try
    select 1/0
  end try begin catch end catch
  </code></pre>
  
  <p>which works in both cases, producing no rows of output.</p>
  
  <p>I have two questions:</p>
  
  <ol>
  <li>Why is there different behavior calling the procedure with and without <code>exec</code>?</li>
  <li>Why does another error of the same error level proceed after the catch?</li>
  </ol>