Database

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 database.'
		goto get_address
	end
fetch next from myCursor into @myAddId, @myAddress, @intCaseId
end
close myCursor
deallocate myCursor

set @query = N'delete tbl_cdf_address'
exec(@query)
return