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

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 09/07/2006 :  13:44:10  Show Profile
The egg council is at work! It's a conspiracy!!

* The preceeding was a vague allusion to an obscure Simpson's episode.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/07/2006 :  14:03:38  Show Profile  Visit gongxia649's Homepage
I have something like this


create proc dbo.usp_CatchAddressException @tableName varchar(500) as
-- alter proc dbo.usp_CatchAddressException @tableName varchar(500) as


begin
set nocount on
declare @sql varchar(700)
declare @error varchar(20)
--set @error = @@error
-- truncate table tempException
set @sql = 'insert hava_dcde.dbo.STREET_EXCEPTION (id_town, id_voter, pre_value, nbr_exception, date_stamp)
select a.id_town, a.id_voter, substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))), ''20'', getdate()
from HAVA_DCDE.dbo.' + quoteName(@tableName)+ ' a
where substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))+1) not in
(select nm_street from HAVA_DCDE.dbo.state_streets ss)'


-- and a.id_town = + @tableName
-- print(@sql)

exec(@sql)
set @error = @@error
if @error <> 0
print 'error'
set nocount off
end


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

SwePeso
Patron Saint of Lost Yaks

Sweden
30121 Posts

Posted - 09/07/2006 :  14:05:13  Show Profile  Visit SwePeso's Homepage
What is your primary key PK_STREET_EXCEPTION made of? Which columns are used for the pk?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36685 Posts

Posted - 09/07/2006 :  14:07:15  Show Profile  Visit tkizer's Homepage
exec(@sql)

RETURN @@ERROR

So now you need to modify the application to handle the error. Show us the application code and we should be able to help you out with it.

Tara Kizer
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/07/2006 :  14:16:42  Show Profile  Visit gongxia649's Homepage
Peso, Id_town and Id_voter is the primary key.

tkizer,
create proc dbo.usp_CatchAddressException @tableName varchar(500) as
-- alter proc dbo.usp_CatchAddressException @tableName varchar(500) as


begin
set nocount on
declare @sql varchar(700)

--set @error = @@error
-- truncate table tempException
set @sql = 'insert hava_dcde.dbo.STREET_EXCEPTION (id_town, id_voter, pre_value, nbr_exception, date_stamp)
select a.id_town, a.id_voter, substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))), ''20'', getdate()
from HAVA_DCDE.dbo.' + quoteName(@tableName)+ ' a
where substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))+1) not in
(select nm_street from HAVA_DCDE.dbo.state_streets ss)'


-- and a.id_town = + @tableName
-- print(@sql)

exec(@sql)
RETURN @@ERROR
end

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.





Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30121 Posts

Posted - 09/07/2006 :  14:25:04  Show Profile  Visit SwePeso's Homepage
try to add following code after the other WHERE clause

and not exists (select * from hava_dcde.dbo.STREET_EXCEPTION q WHERE q.id_town = a.id_town and q.id_voter = a.id_voter)


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/07/2006 14:25:29
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/07/2006 :  14:31:05  Show Profile  Visit gongxia649's Homepage
peso, that works but i thought i was going to use error handling. I still don't understand how to use error handling.





Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30121 Posts

Posted - 09/07/2006 :  14:34:09  Show Profile  Visit SwePeso's Homepage
What everybody been telling you is that your front-end application should respond to the error the stored procedure raises.
The addition I posted just prohibits duplicate values for primary key to be stored.

It is just a fix. I don't even know if this is applicable because your application will never know of this attempt to break business rules.

You really should take some time off to learn some basics of SQL before attempting something that must be done "yesterday".


Peter Larsson
Helsingborg, Sweden

EDIT: Added last two sentences

Edited by - SwePeso on 09/07/2006 14:40:10
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36685 Posts

Posted - 09/07/2006 :  14:34:56  Show Profile  Visit tkizer's Homepage
I wouldn't add the WHERE clause. Your application should handle the errors. You should not do unnecessary checks in the SELECT statement.

Use error handling in the stored procedure. Then modify your application to do the proper thing on error.

And gongxia, you missed my point. The stored procedure will still error, but now your application can capture it since you are returning that information to it. Post the application code like I said and we'll help you with that part.

Tara Kizer
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/07/2006 :  14:43:07  Show Profile
quote:
Originally posted by gongxia649

peso, that works but i thought i was going to use error handling. I still don't understand how to use error handling.



You may want to read this, if you're not too busy:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_05_6zw8.asp




CODO ERGO SUM
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/07/2006 :  14:52:44  Show Profile
quote:
Originally posted by eyechart

and yes, your error message goes away.





G, I misspoke. In TSQL there is not a way to suppress these error messages from being returned to the client application. The error handling that we suggest you use can be used by the application you use to frontend your database. That way, the raw error messages can be suppressed and not displayed to the end user.

vyas has some information on how the raw error suppression works here http://vyaskn.tripod.com/programming_faq.htm#q11

his example is an ASP example, but the same thing can be done in .Net or java or whatever.



-ec


Edited by - eyechart on 09/07/2006 14:56:05
Go to Top of Page

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 09/07/2006 :  14:56:17  Show Profile  Visit robvolk's Homepage
quote:
You may want to read this, if you're not too busy:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_05_6zw8.asp
Damn, you should've posted that yesterday.
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 09/07/2006 :  14:57:42  Show Profile  Visit gongxia649's Homepage
eyechart, you confussed me the whole day yesterday. Thats why the error never went away even i tried my best to do the error handling. i thought i was doing something wrong.

tkizer, I don't even know if we have a front end application, cause' i never seen it.



Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/07/2006 :  15:00:04  Show Profile
quote:
Originally posted by gongxia649

eyechart, you confussed me the whole day yesterday. Thats why the error never went away even i tried my best to do the error handling. i thought i was doing something wrong.




but you want to know about errors don't you? think about that.

quote:
Originally posted by gongxia649

tkizer, I don't even know if we have a front end application, cause' i never seen it.




how do people access the database then? How is the data in the datbase presented to your customers? You don't have everyone fire up QA and kick off ad-hoc sql do you?





-ec

Edited by - eyechart on 09/07/2006 15:02:57
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/07/2006 :  15:01:11  Show Profile
two more articles to read for you that go into some more detail regarding error handling. These articles use the RAISERROR functionality pretty heavily.

http://sqljunkies.com/Article/564F5D77-2F7E-41FB-91C7-353B6D84BF94.scuk

this 2nd article is for SQL2K5 only, it goes into pretty good detail about the new TRY/CATCH functionality.

http://sqljunkies.com/Article/E28ED88D-1780-4F6E-9810-2B88B382C934.scuk


Again, this doesn't talk about suppressing the error in TSQL, he goes into great detail about returning valuable information so you can troubleshoot your code when something goes wrong.



-ec

Edited by - eyechart on 09/07/2006 15:07:34
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36685 Posts

Posted - 09/07/2006 :  15:17:35  Show Profile  Visit tkizer's Homepage
quote:
Originally posted by gongxia649


tkizer, I don't even know if we have a front end application, cause' i never seen it.



Then where are you receiving this error!?

Tara Kizer

Edited by - tkizer on 09/07/2006 15:19:20
Go to Top of Page

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 09/07/2006 :  19:14:54  Show Profile
quote:
Originally posted by tkizer

I wouldn't add the WHERE clause. Your application should handle the errors. You should not do unnecessary checks in the SELECT statement.

Use error handling in the stored procedure. Then modify your application to do the proper thing on error.

And gongxia, you missed my point. The stored procedure will still error, but now your application can capture it since you are returning that information to it. Post the application code like I said and we'll help you with that part.

Tara Kizer


Surely it's better to avoid the "error" altogether!? He knows what's going on - selecting rows that are already there - and as such can tell exactly what they are. It's not really an error condition. Doing it this way is much better IMO (which is why I suggested it on the previous page).
Go to Top of Page

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 09/07/2006 :  22:26:38  Show Profile  Visit timmy's Homepage
Maybe a compromise would be to do a check first and output a specific return parameter if the record(s) already exist.
I'm seeing this a lot in the app I'm working on:
INSERT INTO table.....

IF @@ERROR = 2627
	RETURN 0 --Ignore duplicates
ELSE
	RETURN @@ERROR

fantastic isn't it??..... :-(
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/07/2006 :  22:42:21  Show Profile
I prefer not to have the primary key constraint violation error, so I would code a check for this in my insert statement.


Of course, the really simple solution would be to just not run the insert procedure again after it completes the first time. Maybe I'm missing something, but this seems like a perfectly viable solution.






CODO ERGO SUM
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36685 Posts

Posted - 09/08/2006 :  01:21:19  Show Profile  Visit tkizer's Homepage
Why avoid the error though? By avoiding it, it is doing an unnecessary check. Applications can easily handle the error and post a message to the user. That assumes that he has an application which he indicates that he's not sure he has. That part I don't understand.

Tara Kizer
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