title: Close and Deallocate Cursor and Try-Catch in TSQL
body: <p>I'm writing a stored procedure in TSQL which uses a transaction and a cursor. I'm wondering if I should place <code>CLOSE</code> and <code>DEALLOCATE</code> within a TRY block - can these statements throw?</p>
  
  <p>My code is structured like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  BEGIN TRANSACTION;
  
  DECLARE myCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
  SELECT -- Select statement left out for brevity's sake
  
  BEGIN TRY
      OPEN myCursor
      FETCH NEXT FROM myCursor -- INTO statement left out for brevity's sake
  
      WHILE @@FETCHSTATUS = 0
      BEGIN
          -- Here I process each item in the cursor
          -- and then fetch the items for the next loop run
          FETCH NEXT FROM myCursor
      END;
  
      CLOSE myCursor;
      DEALLOCATE myCursor;
      COMMIT TRANSACTION;
  
  END TRY
  BEGIN CATCH
      CLOSE myCursor;
      DEALLOCATE myCursor;
      ROLLBACK TRANSACTION;
      THROW;
  
  END CATCH
  </code></pre>
  
  <p>I have the following specific questions:</p>
  
  <ul>
  <li>Can <code>CLOSE</code> and <code>DEALLOCATE</code> throw an exception in this specific case? If yes, this would mean that I would need another TRY-CATCH block to safely close the cursor, otherwise, the actual error that occurred in the TRY block could be lost. I couldn't find any info on this <a href="https://docs.microsoft.com/en-us/sql/t-sql/language-elements/close-transact-sql?view=sql-server-2017" rel="nofollow noreferrer">in the official documentation</a>, but this <a href="https://stackoverflow.com/a/28485075/1560623">SO answer</a> suggests it wouldn't do that.</li>
  <li>Should I remove the <code>ROLLBACK TRANSACTION</code> statement in the CATCH block? Is the transaction automatically rolled-back when an exception is thrown?</li>
  <li>Is there any way to avoid the duplicate <code>CLOSE</code> and <code>DEALLOCATE</code> statements in this piece of code?</li>
  </ul>
  
  <p><strong>Update after Martin Smith's comment:</strong></p>
  
  <p>According to <a href="https://stackoverflow.com/a/8743428/1560623">this SO answer</a>, I could declare a cursor variable which is automatically closed and deallocated when the variable goes out of scope. I'm not a 100% sure if this is actually happening: according to the <a href="https://docs.microsoft.com/en-us/sql/t-sql/language-elements/deallocate-transact-sql?redirectedfrom=MSDN&amp;view=sql-server-2017" rel="nofollow noreferrer">DEALLOCATE docs</a>, <code>DEALLOCATE</code> is called, but in the <a href="https://docs.microsoft.com/en-us/sql/t-sql/language-elements/close-transact-sql?view=sql-server-2017" rel="nofollow noreferrer">CLOSE docs</a>, nothing is stated regarding automatic behavior.</p>
  
  <p>Additional questions:</p>
  
  <ul>
  <li><strong>Does using a variable really automatically close and deallocate the cursor?</strong> I have my doubts about that as CLOSE is not called automatically according to the docs. Unless something like reference counting is done internally.add</li>
  <li><strong>If it really works, why call CLOSE at all on a local cursor that is only referenced once?</strong> </li>
  </ul>