



CREATE procedure DBO.[FixDublicateZipcodes] as
SET NOCOUNT ON

declare @countrynumber int
declare @zipcode int

SET ANSI_WARNINGS OFF
declare zip_cur cursor for
  select countrynumber, zipcode
  from zipcodes WITH (nolock)
  group by countrynumber, zipcode
  having count(zipcode) > 1
open zip_cur

fetch next from zip_cur into @countrynumber, @zipcode

declare @country char(15)
declare @city char(150)
declare @zipcodenumber int
declare @action char(10)  set @action = ''
while @@fetch_status = 0
begin
  select @country = CountryName from Countries WITH (nolock) where CountryNumber = @countrynumber
  declare city_cur cursor for
    select zipcodenumber, cityname from zipcodes WITH (nolock) where countrynumber = @countrynumber and zipcode = @zipcode
  open city_cur
  fetch next from city_cur into @zipcodenumber, @city
  if @city is null set @city = 'NULL'
  while @@fetch_status = 0
  begin
    if @city is null
    begin
      set @city = ''
      set @action = 'DELETED:  '
      print @action + convert(char(6), @zipcodenumber) + ', ' + @country + ' - ' + convert(char(7), @zipcode) + ' - ' + @city
      delete from zipcodes where zipcodenumber = @zipcodenumber
    end
    else
    begin
      set @action = ''
      if ((@countrynumber = 1) and (@zipcode = 7800))
        if Upper(@city) <> 'SKIVE' 
        begin
          set @action = 'DELETED:  '
          delete from zipcodes where zipcodenumber = @zipcodenumber
        end
      print @action + convert(char(6), @zipcodenumber) + ', ' + @country + ' - ' + convert(char(7), @zipcode) + ' - ' + @city
    end
    fetch next from city_cur into @zipcodenumber, @city
  end  
  deallocate city_cur

  fetch next from zip_cur into @countrynumber, @zipcode
end

deallocate zip_cur

-- ??? SET ANSI_WARNINGS ON

