T-SQL Coding - How do you code error handling logic in your SQL Server stored procedures?

Question

T-SQL Coding - How do you code error handling logic in your SQL Server stored procedures?

Arjun SreeVastsva

SSCertifiable

Points: 7137
More actions
February 17, 2010 at 5:55 am

#142492

T-SQL Coding - How do you code error handling logic in your SQL Server stored procedures?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

^^^^ R a j u ^^^ (2/17/2010)
T-SQL Coding - How do you code error handling logic in your SQL Server stored procedures?

Bug-free, naturally 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

evansrf SSC-Addicted Points: 419 More actions · Answer 2

Input parameter validation and TRY...CATCH blocks are a great start

TomThomson SSC Guru Points: 104773 More actions · Answer 3

It's a good idea to do some logging when an error are detected, as well as trying to recover or contain the error.

And of course code defensively (detect the impossible happening and deal with it), and (paraphrasing Paul) correctly.

Tom

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

LOL! A 5 year old thread?

Well, since you brought it up, this is how I do it.

Logging and Error Handling for SQL Stored Procedures [/url]

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

I find that many people don't actually know how to write Try/Catch. What they end up doing is throwing the same error but in a manner that obfuscates where the problem actually occurred.

I also agree with Paul White. Write code that won't fail whenever possible. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

Jeff Moden (4/21/2015)
I also agree with Paul White. Write code that won't fail whenever possible. 🙂

Me too! Makes testing a whole lot simpler and quicker!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St