how can I return row information on a failed insert/update in SQL Server?

Question

Ok, so 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:
Cannot insert the value NULL into column 'columnname', table 'Table'; column does not allow nulls. INSERT fails.

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.

Answer 1

This isn't possible AFAIK due to the fact you're not truly working on one record at a time.

Of course for inserts there are no primary keys yet assigned. If you really need that granular level of detail the best advice I can offer is to insert/update one row at a time so you know for sure which one threw the error.

Answer 2

Personally, I check my data for things like that before I try to do an insert. Don't swtich to working one record at a time. Instead write something that will chcek for data that is not there for required fields. If you put the information on what failed into a table variable at the time of the check, it will still be available after the rollback and then you can insert it into a permanent error table to see what records were causing the problem.