Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-SQL Coding - How do you code error handling logic in your SQL Server stored procedures? Expand / Collapse
Author
Message
Posted Wednesday, February 17, 2010 5:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, May 19, 2015 7:27 AM
Points: 2,393, Visits: 1,508
T-SQL Coding - How do you code error handling logic in your SQL Server stored procedures?
Post #866929
Posted Wednesday, February 17, 2010 5:58 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, May 26, 2015 7:33 AM
Points: 9,928, Visits: 11,253
^^^^ 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
SQLblog.com
@SQL_Kiwi
Post #866934
Posted Tuesday, April 21, 2015 12:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 26, 2015 2:32 PM
Points: 209, Visits: 106
Input parameter validation and TRY...CATCH blocks are a great start
Post #1679042
Posted Tuesday, April 21, 2015 2:55 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 8,439, Visits: 10,151
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
Post #1679072
Posted Tuesday, April 21, 2015 6:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:22 AM
Points: 3,936, Visits: 6,183
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



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1679101
Posted Tuesday, April 21, 2015 10:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:36 AM
Points: 37,472, Visits: 34,340
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1679117
Posted Tuesday, April 21, 2015 11:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:22 AM
Points: 3,936, Visits: 6,183
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1679121
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse