| Author |
 Topic  |
|
gongxia649
So Suave
Azores
344 Posts |
 Posted - 09/06/2006 : 13:59:37
|
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)
-- 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 off
end
---------------------------------------------------
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
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 09/06/2006 : 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
Azores
344 Posts |
Posted - 09/06/2006 : 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 1
Violation of PRIMARY KEY constraint 'PK_STREET_EXCEPTION'. Cannot insert duplicate key in object 'STREET_EXCEPTION'.
The statement has been terminated.
|
Edited by - gongxia649 on 09/06/2006 14:13:27 |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 09/06/2006 : 14:21:20
|
quote:
Originally posted by gongxia649
Primary key will throw me an error. I don't want this.
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.
that is exactly what you want. Put error handling in your stored procedure and you will be fine.
-ec |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 09/06/2006 : 14:26:15
|
can you teach me how to put error handling?
is it something like @@rowcount?
|
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 09/06/2006 : 14:32:59
|
can you help me to write the code first, then i will read it.
|
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 09/06/2006 : 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
Azores
344 Posts |
Posted - 09/06/2006 : 14:42:03
|
i actually needed it yesterday.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 09/06/2006 : 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
Azores
344 Posts |
Posted - 09/06/2006 : 14:57:09
|
if you can show me just one example. i will be able to complete my task.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 09/06/2006 : 14:59:24
|
Examples are in the links.
Tara Kizer |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 09/06/2006 : 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
Azores
344 Posts |
Posted - 09/06/2006 : 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 1
Violation of PRIMARY KEY constraint 'PK_STREET_EXCEPTION'. Cannot insert duplicate key in object 'STREET_EXCEPTION'.
The statement has been terminated.
|
Edited by - gongxia649 on 09/06/2006 15:08:44 |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 09/06/2006 : 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
|
Edited by - eyechart on 09/06/2006 15:14:13 |
|
|
|
LoztInSpace
Aged Yak Warrior
876 Posts |
Posted - 09/06/2006 : 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
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 09/07/2006 : 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
USA
15558 Posts |
Posted - 09/07/2006 : 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
Azores
344 Posts |
Posted - 09/07/2006 : 13:20:25
|
eyechart, the error msg doesn't go away.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 09/07/2006 : 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)
USA
6997 Posts |
Posted - 09/07/2006 : 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 Yesterday
CODO ERGO SUM |
|
|
|
Topic |
|
Don't want to insert same values 2 times.
Topic Locked
