SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Don't want to insert same values 2 times.
 New Topic  Topic Locked
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 8

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 09/08/2006 :  03:14:07  Show Profile
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!!
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/08/2006 :  03:21:14  Show Profile
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

Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 09/08/2006 :  04:47:18  Show Profile  Visit mr_mist's Homepage
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. :)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37276 Posts

Posted - 09/08/2006 :  11:24:37  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15679 Posts

Posted - 09/08/2006 :  13:30:32  Show Profile  Visit robvolk's Homepage
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.
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/08/2006 :  14:22:52  Show Profile  Visit gongxia649's Homepage
i'm talking about the play button from Query Analyzer.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/08/2006 :  14:42:51  Show Profile
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/08/2006 :  14:45:27  Show Profile  Visit jsmith8858's Homepage
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
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/08/2006 :  14:59:57  Show Profile  Visit gongxia649's Homepage
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37276 Posts

Posted - 09/08/2006 :  15:01:21  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37276 Posts

Posted - 09/08/2006 :  15:02:21  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/08/2006 :  15:02:40  Show Profile  Visit gongxia649's Homepage
tkizer, i know that. But jsmith8858 doesn't know that i know.

Edited by - gongxia649 on 09/08/2006 15:03:09
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/08/2006 :  15:03:46  Show Profile  Visit gongxia649's Homepage
tkizer, i said i didn't design it.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/08/2006 :  15:04:16  Show Profile
quote:
Originally posted by gongxia649

i'm talking about the play button from Query Analyzer.



Does it take quarters?

And how many plays do you get...

In the good old days we used to get 3 plays for a quarter...

Oh, wait, that was pinball



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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/08/2006 :  15:05:33  Show Profile
and here's a link to a template with error handling

http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx



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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37276 Posts

Posted - 09/08/2006 :  15:07:08  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/08/2006 :  15:11:18  Show Profile
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
Go to Top of Page

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 09/08/2006 :  15:14:13  Show Profile
You guys are saints. Still trying to help after 3 pages going back and forth. God bless you guys.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/08/2006 :  15:15:09  Show Profile
We have other places to vent





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
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/08/2006 :  16:04:43  Show Profile  Visit gongxia649's Homepage
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
Go to Top of Page
Page: of 8 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.41 seconds. Powered By: Snitz Forums 2000