title: When to use Transactions in SQL Server
body: <p>There are lots and lots of questions on HOW to use Transactions. What I want to know is <strong>WHEN</strong>? Under what circumstances? What types of queries? Can Try-Catch blocks suffice instead? Etc...</p>
  
  <p>I've designed a database with ~20 tables and ~20 stored procedures. Currently none of my SPs use a transaction, but there are numerous Try-Catch blocks throughout. The reason is because every time I tried to wrap them in a transaction the SP would cease to function and I would end up with missing data and worse off than had I used Trans.</p>
  
  <p>So again...</p>
  
  <ol>
  <li><strong>When is an appropriate time to use a Transaction?</strong></li>
  <li>As a follow-up question, if I use them, how can I use them in such a way as to ONLY prevent other SPs from accessing the same data at the same time in order to prevent corruption rather than causing my SPs to not function at all?</li>
  </ol>
  
  <p>Here's a little sample SP I wrote for renaming a product:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE PROCEDURE spRenameProduct
      @pKey int = NULL,
      @pName varchar(50)
  AS
  BEGIN
      BEGIN TRY
          IF LTRIM(RTRIM(@pName)) = '' SET @pName = NULL
          IF NOT @pKey IS NULL AND NOT @pName IS NULL BEGIN
              declare @pKeyExisting int = (select MIN(ID) from rProduct where Product like @pName and not ID = @pKey)
              IF @pKeyExisting is null BEGIN
                  update rProduct set IsValid = 1, Product = @pName where ID = @pKey
              END ELSE BEGIN
                  update Request set ProductID = @pKeyExisting where ProductID = @pKey
                  update StatusReport set ProductID = @pKeyExisting where ProductID = @pKey
                  delete from rProduct where ID = @pKey
              END
          END
      END TRY BEGIN CATCH END CATCH
  END
  </code></pre>
  
  <p>Now what if two people were using this at the exact same time? I really don't want to, nor do I have time (unfortunately), to get to fancy. K.I.S.S. is best in this case. :)</p>