title: Using try/catch for update
body: <p>Because I am a newbie I am trying to log out any errors that may occur with stored procedures I write.  I understand Try/Catch in SQL 2005 and error_procedure(), ERROR_MESSAGE() and the other built in functions.  What I can't figure out how to do is capture what record caused the error on an update.</p>
  
  <p>I could probably use a cursor and loop through and update a row at a time.  Then in the loop set a value and report on that value.  But that seems to defeat the purpose of using SQL.</p>
  
  <p>Any ideas or pointer on where to research this issue greatly appreciated.  I do not fully understand RowNumber() could I use that somehow?  Kind of grasping at straws here.</p>
  
  <p>cheers and thanks </p>
  
  <p>Bob </p>
  
  <p>I am using SQL 2005.</p>
  
  <p>Edit</p>
  
  <p>I really do not want to use transactions for most of this, as it is just for reporting purposes.  So an example of what I am doing is:</p>
  
  <pre><code>/******************************************************************************
     Now get update the table with the current worker.  That depends on the
     current status of the loan.
  ******************************************************************************/
  
  UPDATE #table SET currWorker = tblUser.UserLogonName
        FROM tblUser
              JOIN tblLoanInvolvement ON tblLoanInvolvement.invlUnderwriterDeptID = tblUser.userID 
              WHERE tblLoanInvolvement.LOANid = #table.loanid
              AND #table.currstatus in('R_UW_Approved','R_Submitted to Underwriting')
  
  
    UPDATE #table SET currWorker = tblUser.UserLogonName
    FROM tblUser
          JOIN tblLoanInvolvement ON tblLoanInvolvement.invlProcessorID  = tblUser.userID 
          WHERE tblLoanInvolvement.LOANid = #table.loanid
          AND #table.currstatus in('R_UW Approved With Conditions','R_Loan Resubmitted','R_UW_Suspended','R_Submitted to Processing')
  
    UPDATE #table SET currWorker = tblUser.UserLogonName
    FROM tblUser
          JOIN tblLoanInvolvement ON tblLoanInvolvement.invlCloserID = tblUser.userID 
          WHERE tblLoanInvolvement.LOANid = #table.loanid
          AND #table.currstatus in('R_Docs Out','R_Ready to Close','R_Scheduled to Close and Fund','Scheduled To Close')
  </code></pre>
  
  <p>So if one row does not update correctly I do not want to loose the whole thing.  But it would be very handy to know the value of #table.loanid that caused the problem.  </p>
  
  <p>Thanks for your time.</p>