| Author |
 Topic  |
|
LoztInSpace
Aged Yak Warrior
878 Posts |
 Posted - 09/08/2006 : 03:14:07
|
Avoidable errors should be avoided. This appears to be a programming error. What would you tell the user? "I tried to do something that isn't allowed to happen. I know exactly what went wrong and why, but I didn't do anything about it even though I could have prevented it and not bothered you. As it stands, your transaction failed and whatever it was you were trying to do remains undone. I could have prevented that.....but I didn't."
You get the picture!!
|
 |
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 09/08/2006 : 03:21:14
|
quote:
Originally posted by LoztInSpace
Avoidable errors should be avoided. This appears to be a programming error. What would you tell the user? "I tried to do something that isn't allowed to happen. I know exactly what went wrong and why, but I didn't do anything about it even though I could have prevented it and not bothered you. As it stands, your transaction failed and whatever it was you were trying to do remains undone. I could have prevented that.....but I didn't."
I think Tara's point is that the extra check is potentially an expensive operation. The handling of the error when the insert fails is not, becuase you should be checking for an error any way.
-ec
|
|
|
|
mr_mist
Grunnio
United Kingdom
1870 Posts |
Posted - 09/08/2006 : 04:47:18
|
quote:
Originally posted by gongxia649
should generate the same records. But i don't want it to be inserted into the table.
Because i might accidentally hit run 2 times.
The solution is to be more careful then.
-------
Moo. :) |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 09/08/2006 : 11:24:37
|
quote:
Originally posted by LoztInSpace
Avoidable errors should be avoided. This appears to be a programming error. What would you tell the user? "I tried to do something that isn't allowed to happen. I know exactly what went wrong and why, but I didn't do anything about it even though I could have prevented it and not bothered you. As it stands, your transaction failed and whatever it was you were trying to do remains undone. I could have prevented that.....but I didn't."
You get the picture!!
I'm not saying that you don't do anything about it. You attempt the insert and then receive the PK error. Your application receives the error, then you let the user know that the row could not be inserted because it is a duplicate. Why bother with the extra WHERE clause? If you do use the extra WHERE clause, you're going to have to tell the user that it couldn't be done anyway so you'll need to check @@ROWCOUNT or something similar.
Tara Kizer |
|
|
|
robvolk
Most Valuable Yak
USA
15568 Posts |
Posted - 09/08/2006 : 13:30:32
|
I know it's waaaaaaay back on page 1, but:quote:
But i don't want it to be inserted into the table. Because i might accidentally hit run 2 times.
How about changing the app code to disable the Run button after you click it? Then you can't run it twice, accidentally or otherwise. |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 09/08/2006 : 14:22:52
|
| i'm talking about the play button from Query Analyzer. |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 09/08/2006 : 14:42:51
|
quote:
Originally posted by gongxia649
i'm talking about the play button from Query Analyzer.
then just deal with the error with the PK violation.
-ec |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 09/08/2006 : 14:45:27
|
gong -- You have been saying that you wish for your stored procedure to gracefully return an error code or status value if the INSERT fails because the data is already there. that's fine. But then you say that the only place that you are calling this stored procedure will be from Query Analyzer????? If so, then what is the difference? Either way, the statement fails, the rows are not added, a message is displayed to you telling you why ... what is the big deal? Why go through all this trouble? why not leave it as is?
Typically, a client *Application* is written by someone and that is where these stored procedures are called. The application may be a website or an executable or even a reporting tool like Crystal Reports or Reporting Services. The client app would then check output parameters or return value and then decide how to handle the situation -- display a message to the user, or just ignore, or even crash to desktop .... Are you telling us that there is no client? This makes no sense. How will regular users interact with this database and these stored procedures that you are designing? If a "regular user" is using Query Analyzer, then that user should be sophisticated enough to understand what a primary key violation is.
What you are asking for and the situation that you are describing makes no sense .... are you sure that you fully understand the project you are working on?
- Jeff |
Edited by - jsmith8858 on 09/08/2006 14:47:20 |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 09/08/2006 : 14:59:57
|
jsmith8858, no no no.
i just want to learn how to use error handling, then EYEchart says using error handling, the error on QA will go away.
and are you sure you understand my post? |
Edited by - gongxia649 on 09/08/2006 15:01:03 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 09/08/2006 : 15:01:21
|
The error will not go away unless you use a WHERE clause. That has already been explained to you. Perhaps you need to re-read this thread.
Tara Kizer |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 09/08/2006 : 15:02:21
|
quote:
Originally posted by gongxia649
and are you sure you understand my post?
Are you sure you understand why your database design is terrible?
Tara Kizer |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 09/08/2006 : 15:02:40
|
| tkizer, i know that. But jsmith8858 doesn't know that i know. |
Edited by - gongxia649 on 09/08/2006 15:03:09 |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 09/08/2006 : 15:03:46
|
| tkizer, i said i didn't design it. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 09/08/2006 : 15:07:08
|
quote:
Originally posted by gongxia649
tkizer, i know that. But jsmith8858 doesn't know that i know.
We're not sure what you know. It's not like we know you personally.
How can someone be writing an application whose front-end is Query Analyzer!? I don't get it.
Tara Kizer |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 09/08/2006 : 15:11:18
|
quote:
Originally posted by gongxia649
jsmith8858, no no no.
i just want to learn how to use error handling, then EYEchart says using error handling, the error on QA will go away.
and are you sure you understand my post?
And that's not entirely correct either...at least in versions earlier than SQL 2k5...some errors you just can't handle.
But if you like at my link, I use that template for 99% of all sprocs
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
|
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 09/08/2006 : 15:14:13
|
| You guys are saints. Still trying to help after 3 pages going back and forth. God bless you guys. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 09/08/2006 : 16:04:43
|
quote:
Originally posted by tkizer
quote:
Originally posted by gongxia649
tkizer, i know that. But jsmith8858 doesn't know that i know.
We're not sure what you know. It's not like we know you personally.
How can someone be writing an application whose front-end is Query Analyzer!? I don't get it.
Tara Kizer
QA error
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_STREET_EXCEPTION'. Cannot insert duplicate key in object 'STREET_EXCEPTION'.
The statement has been terminated.
error
|
|
|
|
Topic |
|
Don't want to insert same values 2 times.
Topic Locked
