Stop TSQL from raising errors to caller

Question

Post reply

Stop TSQL from raising errors to caller

Viewing 5 posts - 16 through 20 (of 20 total)

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

Sergiy SSC Guru Points: 110209 More actions · Answer 1

How many do you expect?

Duplication, not existence, datatype mismatch, what else?

And you need to cover them all any way.

SQL Server works at background, it does not presume interaction with a front end user.

So, all errors must be worked out without human intervention what brings us to the point where you need to program system reaction on ALL possible errors.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

Jportelas (11/28/2008)
Thanks Sergyi, Matt and Jeffrey, so there is no way to achieve this in T-SQL for SQL Server 2000?
Seems like trying to cover all the failure scenarios is the only way.

Yes, there is...

Right now, you insert into the table rather "blindly" without any regard to what may fail. You're actually programming by exception rather than by plan. Basically, you throw some data at a table and if it doesn't fit according to constraints, SQL is gonna tell the user. It's like throwing the proverbial spaghetti at the wall to see what sticks.

The best way to do this would be to check the data and see if the table constraints would cause an exception like a primary key violation BEFORE you try to do the insert.

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Jeff Moden (11/28/2008)
Jportelas (11/28/2008)
Thanks Sergyi, Matt and Jeffrey, so there is no way to achieve this in T-SQL for SQL Server 2000?
Seems like trying to cover all the failure scenarios is the only way.
Yes, there is...
Right now, you insert into the table rather "blindly" without any regard to what may fail. You're actually programming by exception rather than by plan. Basically, you throw some data at a table and if it doesn't fit according to constraints, SQL is gonna tell the user. It's like throwing the proverbial spaghetti at the wall to see what sticks.
The best way to do this would be to check the data and see if the table constraints would cause an exception like a primary key violation BEFORE you try to do the insert.

Agreed, but as long as "all errors" = all data VALIDATION errors, etc..., i.e. rules you've coded for (like - there should be a foreign key, and the key need to be present in the reference table; dates need to be between this and that, etc...). There's still a set of errors you can't account for even after all of the defensive coding, especially with 2000 and its more limited error handling.

By all means - coding to validate up front will allow you to get past all of those errors you get from "just throwing it against the way to see what sticks" (which should be 99% of the typical errors). You STILL might catch the occasional error, at which point those will likely be system-related than data quality-related.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Sergiy SSC Guru Points: 110209 More actions · Answer 4

Matt Miller (12/1/2008)
By all means - coding to validate up front will allow you to get past all of those errors you get from "just throwing it against the way to see what sticks" (which should be 99% of the typical errors).

Once again: there is no one to see what sticks.

Who suppose to make decisions on what to do with errors happening on background process not having front-end interface?

Or you prefer to be woken up in the middle of the night by SMS messages from the server because it needs your instruction what to do with another set of errors?

_____________
Code for TallyGenerator

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Sergiy (12/1/2008)
Matt Miller (12/1/2008)
By all means - coding to validate up front will allow you to get past all of those errors you get from "just throwing it against the way to see what sticks" (which should be 99% of the typical errors).
Once again: there is no one to see what sticks.
Who suppose to make decisions on what to do with errors happening on background process not having front-end interface?
Or you prefer to be woken up in the middle of the night by SMS messages from the server because it needs your instruction what to do with another set of errors?

You misread me. I'm agreeing you SHOULDN'T have that kind of code. You shouldn't trust that your data coming in is going to conform to the rules, and you shouldn't rely on your last line of defense (i.e. constraints) to do all of your validations.

All I was pointing out is that only weeds out those kinds of errors. It won't help you catch the "oops my linked server is toast/my tempDB is full" errors (which of course shouldn't happen either, but do anyway).

Catching ALL errors is an awfully big request. Preventing the ones you can (so that you don't HAVE to catch them) is the goal (which is what Jeff is advocating).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?