Author |
Topic |
gongxia649
So Suave
344 Posts |
Posted - 2006-09-06 : 13:59:37
|
create proc dbo.usp_CatchAddressException @tableName varchar(500) as-- alter proc dbo.usp_CatchAddressException @tableName varchar(500) asbegin set nocount on declare @sql varchar(700) -- 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 nocount offend---------------------------------------------------when i run this SP 2 times, it will insert the values 2 times.what should i do if i don't want to insert the same values 2 times?I'm confussed, Should i use update? any input will be appreciated. |
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-09-06 : 14:07:10
|
when you run it twice, should the 2nd run generate different data or should it actually be the same?How about using a primary key or unique key on the table you are inserting into.-ec |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-06 : 14:09:12
|
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.Primary key will throw me an error. I don't want this.Server: Msg 2627, Level 14, State 1, Line 1Violation of PRIMARY KEY constraint 'PK_STREET_EXCEPTION'. Cannot insert duplicate key in object 'STREET_EXCEPTION'.The statement has been terminated. |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-09-06 : 14:21:20
|
quote: Originally posted by gongxia649Primary key will throw me an error. I don't want this.Server: Msg 2627, Level 14, State 1, Line 1Violation of PRIMARY KEY constraint 'PK_STREET_EXCEPTION'. Cannot insert duplicate key in object 'STREET_EXCEPTION'.The statement has been terminated.
that is exactly what you want. Put error handling in your stored procedure and you will be fine.-ec |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-06 : 14:26:15
|
can you teach me how to put error handling?is it something like @@rowcount? |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-06 : 14:32:59
|
can you help me to write the code first, then i will read it. |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-09-06 : 14:39:21
|
quote: Originally posted by gongxia649 can you help me to write the code first, then i will read it.
so, this sproc is so critical that you have to have it working in the next 30 seconds?how about you take 5 minutes and read that article. You might learn something.-ec |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-06 : 14:42:03
|
i actually needed it yesterday. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-06 : 14:47:40
|
Then you'd better start reading it quickly! We aren't here to do your job for you. Ec already provided what you need to be able to figure this out.Tara Kizer |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-06 : 14:57:09
|
if you can show me just one example. i will be able to complete my task. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-06 : 14:59:24
|
Examples are in the links.Tara Kizer |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-09-06 : 15:02:54
|
quote: Originally posted by gongxia649 if you can show me just one example. i will be able to complete my task.
gong, rtfa!damn, the whole thing is there. The 2nd article has all the examples btw, if you just want to skip to the back of the book.-ec |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-06 : 15:07:34
|
i can't find anything error handling for primary key in the articles.i just found.This option applies to unique indexes only. It is not available for PRIMARY KEY or UNIQUE constraints.i will also know. once i write the code for error handling. Will i still see the msg?Server: Msg 2627, Level 14, State 1, Line 1Violation of PRIMARY KEY constraint 'PK_STREET_EXCEPTION'. Cannot insert duplicate key in object 'STREET_EXCEPTION'.The statement has been terminated. |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-09-06 : 15:12:16
|
ok, you are killing me.did you see the part where it talks about @@ERROR? (you can look up @@ERROR in BOL for more information) Or the part about decalaring a variable called @ERR to store the value of @ERROR? what about the section on error handling with dynamic SQL. that might apply to you also since you are executing dynamic sql. Did you see the sample code in the 2nd article showing you this stuff in action? You can literally cut and paste the error handling code out of the 2nd article and plop it down into your sproc and be done.and yes, your error message goes away.-ec |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2006-09-06 : 23:20:47
|
Or change your WHERE clause to exclude rows that are already there: WHERE ... and a.id_town not in(select id_town from street_exception).Your whole design is pervy though - dynamic SQL on the table name. Yuk. Don't read the normalisation guidelines while you're not reading the other articles! :) |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-09-07 : 09:50:02
|
Gong,did you get this to work? I'm curious if you bit the bullet and read through those articles.-ec |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-09-07 : 09:53:24
|
Hmmm, you don't have or take time to read articles about using SQL Server properly, and you have problems with the code you're writing.Coincidence????...or...CONSPIRACY???I love how you couldn't take time to read, but had plenty of time to write a response(s) saying you didn't have time to read it.Do you have other people chew your food for you too? Or do you have someone else hire them? |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-09-07 : 13:20:25
|
eyechart, the error msg doesn't go away. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-07 : 13:24:26
|
quote: Originally posted by gongxia649 eyechart, the error msg doesn't go away.
Please post your new code with the error handling section.Tara Kizer |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-07 : 13:42:56
|
quote: Originally posted by robvolk Hmmm, you don't have or take time to read articles about using SQL Server properly, and you have problems with the code you're writing.Coincidence????...or...CONSPIRACY???I love how you couldn't take time to read, but had plenty of time to write a response(s) saying you didn't have time to read it.Do you have other people chew your food for you too? Or do you have someone else hire them?
Custom title:Needed it YesterdayCODO ERGO SUM |
|
|
Previous Page&nsp;
Next Page
|