• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

Handling errors in T-SQL

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
Dale Fye
Dale Fye
2 Solutions
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Can you post your MERGE query please.
Vitor MontalvãoMSSQL Senior EngineerCommented:
What is massage data?
(...) to massage some data (...)

It really needs to be with T-SQL? Integration Services can do the job for you and showing the progress and errors if any.

For handling errors in T-SQL you have the TRY ... CATCH. Full explanation with examples in MSDN.
Dale FyeAuthor Commented:

The Merge procedure really is not important other than that is where I encountered the error.  I understand why the error occurred (multiple records in the source table matched the joining fields in the target table).


Still a newbie with SSIS, but may eventually may move some of this there.  Much of the massaging of the data involves pushing the data from Access into SQL Server staging tables, then identifying discrepancies in the data before performing the SQL SPs.  Thanks for the reference to Try ... Catch, believe that is what I'm looking for.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.


"massage" is used in the sense of  "to adjust"; to adjust the data to suit some purpose e.g. to remove malformed data or ensure dates are in fact dates etc.
Vitor MontalvãoMSSQL Senior EngineerCommented:
And I thought was a typo :)
Sorry but English is not my mother tongue language so sometimes have these little surprises. Living and learning. Thanks for the explanation.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>1.  skip steps that rely on an earlier event in the process.
This is going to be tricky, as you're going to have to map out all of these threads, or put everything in IF blocks to handle dependancies, like this..

IF @some_dependant_process = Y
   IF EXISTS (SELECT some_column FROM some_table WHERE process_name='some_name' AND some_flag='Y')
      --- Do good path stuff here
      -- Do not so good stuff here
   --- Do really not good stuff here

Note that in the above block, if the -- contains more than one line, you'll have to surround it with BEGIN up top and END at the bottom.   (Yes this is different from Access)

>2.  display an error message in my tbl_Processes
Paint us a picture of what you're trying to pull off here.

As far as handling errors, the above link to TRY..CATCH is the way to go.
Dale FyeAuthor Commented:

I like the idea of using a variable and an If statement to execute the subsequent steps in the process.  So, I think in this case, I would do something like:
DECLARE @MergeSuccess bit
DECLARE @RecordsAffected int 
DECLARE @Message as nvarchar(150)

Set @MergeSuccess = 1


    SELECT 1/0;
    SET @RecordsAffected = @@ROWCOUNT

    Set @MergeSuccess = 0
    SET @RecordsAffected = 0
    SET @Message = ERROR_MESSAGE()
IF @MergeSuccess = 1
    TRUNCATE Table zz_Staging_tanks;

INSERT INTO tbl_Processes (ProcessName, RecordCount, Comment, DT_When)
 SELECT 'Merge X', @RecordsAffected, @Message, dbo.TodaysDate()

Open in new window

What I've been doing with tbl_Processes is keeping track of when each process was run, # of records affected.  In Access, I have a query and form that identifies all of the processes, the most recent run, then # of records affected.  I use conditional formatting to highlight when the RecordsAffected = 0; this gives me an indication that the data coming from the source did not get pulled properly, or an error of some sort occurred.  If the most recent [DT_When] is not today, that gets flagged as well.  And if RecordsAffected is outside 1.5 standard deviations from the average for that process, I color code that yellow, as something that needs to be looked into.
Dale FyeAuthor Commented:
Thanks, guys.

@Vitor, I assumed the English was not your primary language when the link you posted sent me to the German language version of MSDN.
Vitor MontalvãoMSSQL Senior EngineerCommented:
My german's worst than my English. It's just because I'm in Switzerland and my browser assuming that I know german :)
Dale FyeAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now