title: how can I return row information on a failed insert/update in SQL Server?
body: <p>Suppose I am doing an insert or an update on a table.  So in the BEGIN CATCH/END CATCH I can define a variable to ERROR_MESSAGE() and get back my error message:</p>
  
  <blockquote>
    <p>Cannot insert the value NULL into column 'columnname', table 'Table'; column does not allow nulls. INSERT fails.</p>
  </blockquote>
  
  <p>Is there any way I could return say the primary key of the offending record or anything to identify which row actually failed?  I rollback the transaction on failure so it's not like I can look at the 'last' record to see the next one which has the problem.</p>