I'm an Access MVP moving into SQL Server in a big way.
I'm writing a series of T-SQL procedures to massage some data from several different data sources and insert it into a variety of tables in SQL Server.
I am keeping track of these processes in tbl_Processes, where I update the status of each of the processes as they complete, with a comment "updated x records from abc", "inserted y records from 123" and a date time stamp, so that I can display this information in a dashboard.
One of the processes is a MERGE query which simultaneously updates and appends records. What I found out today is that one of the data sources occasionally has duplicate records for the combination of fields that are used in the MERGE join. When this happened, I got an error and the procedure did not complete properly.
How do I trap for errors in T-SQL so that I can
1. skip steps that rely on an earlier event in the process.
For example, if the Merge is successful, I truncate a staging table. If the merge fails, I don't want to delete the records from the staging table, I want to be able to review them from Access, make the appropriate changes and then re-run that particular SP.
2. display an error message in my tbl_Processes