September 6, 2006 at 1:01 pm
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.
September 6, 2006 at 1:22 pm
I am assuming that id_town and id_voter are unique. so u need to add "NOT EXISTS" clause to you select statement.
Here you go:
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 not exists (Select 1 from hava_dcde.dbo.STREET_EXCEPTION DEST where DEST.id_town = a.id_town and DEST.id_voter = a.id_voter)'
-- and a.id_town = + @tableName
-- print(@sql)
exec(@sql)
set nocount off
end
Hope this helps.
Thanks
Sreejith
September 7, 2006 at 8:59 am
You could add an index to your table that requires the keys to be unique. If you then try to insert a duplicate key, the server will refuse to add the record and throw an error you could catch.
September 7, 2006 at 11:04 am
how can i write the code to catch the error?
is it somehting like
if @@error <> 0
print 'error'
September 7, 2006 at 12:00 pm
yes, each error has a specific number, i don't recall off the top of my head which number is duplicate key violation but we use code that looks like
if @@rowcount <> 1 or @@error <> 0 begin
-- handle error here
return 1
end -- return error
you can examine the value of @@error to find out what error number you have
September 7, 2006 at 12:13 pm
i think its 2627
so it will be like
if @@error = 2627
print 'error'
May 1, 2007 at 9:03 am
1
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply