SP Unexpected Behavior

eddieangel

I have a procedure that checks to see if a user added address exists in a remote database. If the address exists, the procedure retrieves the id and updates the local DB with that id. If it does not exist, the procedure adds the address to the remote database, retrieves the new id, and updates the local database with the new id. Unfortunately, without any apparent pattern some times several addresses are lumped together into one single new address id. Can someone give me a second pair of eyes on this procedure to see where it might be doing this? I am also open to improvements.

USE [RemoteCapture]
GO
/****** Object: StoredProcedure [dbo].[usp_resolveAddressProblems] Script Date: 02/01/2013 09:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
/****** Object: StoredProcedure [dbo].[usp_resolveAddressProblems] Script Date: 10/06/2009 12:41:21 ******/

ALTER proc [dbo].[usp_resolveAddressProblems] (@servername varchar(100))

as

declare @query nvarchar(650)
declare @myAddId int
declare @myAddress varchar(100)
declare @newId int
declare @cursorString varchar(2000)
declare @intCaseId int
declare @remoteId int = 0

set @cursorString = 'declare myCursor cursor for select intAddressId, txtAddress, intCaseId from tbl_cdf_address where bitUserAdded = 1'
exec(@cursorString)
open myCursor
fetch next from myCursor into @myAddId, @myAddress, @intCaseId
while @@FETCH_STATUS = 0
begin
get_address:
set @remoteId = 0
set @query = N'select @remoteId = intAddressId from "' + @servername + '".depo.dbo.tbl_cdf_address where txtAddress = ''' + @myAddress + ''' and intCaseId = ' + CAST(@intCaseId as varchar(10))

exec sp\_executesql @query, N'@remoteId int output', @remoteId output
if @remoteId != 0 
begin
	set @query = N'update tbl\_cdf\_observation set intAddressId = ' + cast(@remoteId as varchar(10)) + ' where intAddressId = ' + CAST(@myAddId as varchar(10)) + ' and intCaseId = ' + CAST(@intCaseId as varchar(10))
	exec(@query)
	set @query = N'update tbl\_cdf\_inspectionNotes set intAddressId = ' + cast(@remoteId as varchar(10)) + ' where intAddressId = ' + CAST(@myAddId as varchar(10)) + ' and intCaseId = ' + CAST(@intCaseId as varchar(10))
	exec(@query)
	print 'Updated ' + @myAddress
end
else
begin
	set @query = N'insert "' + @servername + '".depo.dbo.tbl\_cdf\_address (intCaseId, txtAddress, bitActive) values (' + CAST(@intCaseId as varchar(50)) + ',''' + @myAddress + ''',1)' 
	exec(@query)
	print 'Added ' + @myAddress + ' to