CREATE PROCEDURE DBO.FindDublicateCountries AS
SET NOCOUNT ON
/*
  Created 20050203 Kent

  Deletes duplicate countries
*/

DECLARE
  @CountryCode VARCHAR(3), 
  @CountryNumber INT

DECLARE
  @OldCountryCode VARCHAR(3)

SET
  @OldCountryCode = ''
  

DECLARE #CountryCur CURSOR FORWARD_ONLY FOR
SELECT CountryCode, CountryNumber FROM Countries WITH (NOLOCK) 
ORDER BY CountryCode, CountryNumber

OPEN #CountryCur

FETCH NEXT FROM #CountryCur INTO @CountryCode, @CountryNumber
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  IF @OldCountryCode = @CountryCode
  BEGIN
    PRINT 'Deleting CountryCode: '+@CountryCode
    DELETE Countries WHERE CountryNumber = @CountryNumber
  END;

  SET @OldCountryCode = @CountryCode
	FETCH NEXT FROM #CountryCur INTO @CountryCode, @CountryNumber
END

CLOSE #CountryCur
DEALLOCATE #CountryCur

