Learning to handle errors better can be one of the skills that will really set you apart from other SQL Developers. You can do constant unit testing after each change, but odds are you’ll never hit ever scenario your code will face. There is a better way.

If you have programmed in any language like PHP, C#, etc, I’m sure you’ve used the TRY..CATCH construct. If not, you’re in for a treat. This construct can actually catch once-fatal errors, and allow you to handle them cleanly, without passing the error along to your user interface. Here’s the basic template.

BEGIN TRY
..T-SQL statements that could fail (aka, ALL of your code!)..
END TRY
BEGIN CATCH
..What do you want to do in case you do have an error?..
END CATCH

Let’s use a really simple example. We’re going to build a function called divide. I know we already have one… but we’re building one that has error handling built in.

Now, if it fails, you get a pretty error, and the result comes back 0. In future posts, I’ll build on the TRY..CATCH construct. You can do more with this, you can detect for certain error codes, you can get the error the line number occurred on, and more! (Think about coupling this with TRANSACTION…imagine the possibilities!) But this is enough for today. Master this much, and I’ll get you a new sword!

Trackbacks/Pingbacks

[…] a trick: you have in order to get data out of these stored procedures, you have to be inside a TRY..CATCH block. Check out my previous article, for more info on how to set up and use a TRY..CATCH […]

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Search

What I Do…

Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

If you’ve seen my OnPrem to Azure presentation, then you know one of the two big reservations I had about windows azure was we couldn’t ...

Connect

Subscribe to our e-mail newsletter to receive updates.

Recommendations

Shannon is a very valuable asset to any development team, as one who is always looking ahead and trying to improve the status quo. Shannon’s work ethic is great, he is always willing to do anything in his power to get things accomplished for the team. Shannon doesn’t just become a team member who shows up to put in his 8 hours and go home… he gets emotionally involved and attached with the team, and treats them like family. I look forward to another opportunity to work with Shannon in the future.

John A. Cundiff Jr., Senior Systems Team

Shannon and I found each other through a former co-worker and friend. We’ve been able to develop a mutually beneficial consulting/brainstorming relationship, each of us having experience in areas where the other is growing.

Shannon has proven to be a fantastic resource, educator and leader over the time that I have been working with him. He is always very responsive to my questions and more than willing to go out of his way to help. Anyone that gets a chance to attend one of his talks or work with him in any way should consider themselves lucky!

Holly Czuba, Web & Graphic Design, Bare Bulb Marketing

Shannon is a dedicated and reliable team player. He is technically proficient and responsive to requests, always willing to help.