title: Best way to combine Insert, Update and Delete transaction in SQL Server 2008?
body: <p>I have login process for my application that should run three different transactions if user successfully passed the authentication. Here is example of my current code:</p>
  
  <pre><code>&lt;cftransaction action="begin"&gt;  
      &lt;cftry&gt;
          &lt;cfset local.appStruct = structNew()&gt;
          &lt;cfset local.appStruct.AccountID = UserResults.AccountID&gt;
          &lt;cfset local.appStruct.UserName = UserResults.UserName&gt;
          &lt;cfset local.appStruct.Email = UserResults.Email&gt;
          &lt;cfset session.AccountInfo = appStruct&gt;
          &lt;cfset session.LoggedIn = true&gt;
  
          &lt;cfquery name="trackLogin" datasource="#dsn#"&gt;
              DELETE
              FROM FailedLogins
              WHERE LoginUN = &lt;cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(arguments.username)#" maxlength="50"&gt;
  
              UPDATE Accounts
              SET LockedUntil = NULL
              WHERE UserName = &lt;cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(arguments.username)#" maxlength="50"&gt;
  
              INSERT INTO AccountLogins (
                  AccountID,
                  Login
              )VALUES(
                  &lt;cfqueryparam cfsqltype="cf_sql_idstamp" value="#UserResults.AccountID#"&gt;,
                  CURRENT_TIMESTAMP
              )
          &lt;/cfquery&gt;
  
          &lt;cfset local.fnResults = {status : "200"}&gt;
  
          &lt;cfcatch type="any"&gt;
              &lt;cftransaction action="rollback" /&gt;
                  &lt;cfset local.fnResults = {error : cfcatch, status : "400", message : "Error! Please contact your administrator."}&gt;
          &lt;/cfcatch&gt;
      &lt;/cftry&gt;
  &lt;/cftransaction&gt;
  </code></pre>
  
  <p>In the code above first I set some <code>session</code> variables and then I clean up the Failed logins, then update the flag and insert some info in Account login table. First I would like to move this in Store Procedure. Then I'm wondering if there is a good way to combine these three transactions in one or they should be separate Stored procedures? If anyone have advise or good reason why one way or the other please let me know. Thank you.</p>