A cursor with the name 'tmpCursor' already exists.

ALTER procedure [dbo].[sp_ues_workflow_one_update_address_relationship]  --(@clientid varchar(32))
as
declare
	@currentDate datetime,
	@dateEstablished datetime,
	@dateDifference integer,
	@addressCount integer,
	@currentAddress varchar(20),
	@projectStartDate datetime,
	@projectStart_DateDifference integer,
	@addressRelationship varchar(100),
	@salesAccountType varchar(100),
	@tmp varchar(500),
	@createDate datetime,
	@clientid varchar(32)
	
	
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 begin
 
	set @currentDate = getDate()
	
	
 
	begin
	
	declare tmpClientCursor cursor for 
		select clientid from claddress
	
	open tmpClientCursor
	fetch next from tmpClientCursor into @clientID
	while @@fetch_status = 0 
	--determine that if the current client does not have a sales account type of regular then exit the stored proc
	
	
	declare tmpCursor cursor for 
		select custSalesAccountType from clientCustomTabFields Where clientID = @clientID
			
	open tmpCursor
	fetch from tmpCursor into @salesAccountType
	
	close tmpCursor
	deallocate tmpCursor
	
	if @salesAccountType = 'Regular'
	begin
		--loop through all of the addresses for the current client id
		declare addressCursor cursor for 
			select address from clAddress Where clientID = @clientID
			
		open addressCursor
		fetch from addressCursor into @currentAddress
		
		while @@fetch_status = 0
		begin
		
				declare dateEstablishedCursor cursor for 
				Select IsNull(dateEstablished, '01/01/1901') dateEstablished, isNull(addressRelationship, '') addressRelationship From clAddress Where ClientID = @clientID And Address = @currentAddress
 
				open dateEstablishedCursor
				fetch from dateEstablishedCursor Into @dateEstablished, @addressRelationship
				
				close dateEstablishedCursor
				deallocate dateEstablishedCursor
				
				--conditions for stored procedure
				--1. if the client is not associated with any projects then the address relationship is 'Prospect'
				--2. if the client's address relationship is 'Prospect' the only way the it can change is a change to 'New'
				--3. if a client's address relationship is 'New' the only that it can change is 'existing'
						--a.  however, it could be associated with new projects based upon the start date of the project.  In that case it was stay new and 
						--the date established will be changed to start date of the project
						--b.  if there are no new project's associated with the client then Address Relationship changes to 'Existing' and Date Established Changes to the date the stored proc runs
				--4. if a client's address relationship is 'Existing' then the only way that it can change is to go to 'Former' or go to 'New'
						--a.  the same conditions as three apply here
						
				if @addressRelationship = '' 
				begin	
					print 'Address Relationship is blank'
					--determine if there any projects associated with the client and address
					declare projectCursor cursor for 
					select max(startdate) startdate, max(createdate) from pr where billingclientid = @clientID and clBillingAddr = @currentAddress and wbs2 = '' and wbs3 = '' --will need to ask if this will be clientid or billlingclientid
					
					open projectCursor 
					fetch from projectCursor into @projectStartDate, @createDate
					
					close projectCursor
					deallocate projectCursor
			
					if @projectStartDate is null
					begin
					--change the address relationship to Prospect since there are no projects associated with the client
						update clAddress set AddressRelationship = 'Prospect' where clientID = @clientID and Address = @currentAddress
					end
					else
					begin
						update clAddress set AddressRelationship = 'New', DateEstablished = @createDate where clientID = @clientID and Address = @currentAddress
					end
				end
				
				
				if @addressRelationship = 'Prospect'
				begin
					--if the client's address relationship is Prospect then determine if the client is associated with project 
					--if so then change address relationship to new and date established to createdate of the Project
				
					declare projectCursor cursor for 
					select max(startdate) startdate, max(createdate) createdate  from pr where billingclientid = @clientID and clBillingAddr = @currentAddress and wbs2 = '' and wbs3 = '' --will need to ask if this will be clientid or billlingclientid
					
					open projectCursor 
					fetch from projectCursor into @projectStartDate, @createDate
					
					close projectCursor
					deallocate projectCursor
			
					if @createDate is not null
					begin
					--change the address relationship to Prospect since there are no projects associated with the client
						update clAddress set AddressRelationship = 'New', DateEstablished = @createDate where clientID = @clientID and Address = @currentAddress
					end
				
				end 
				
				if @addressRelationship = 'New'
				begin
					--if the address relationship is 'New' then the only way that it can change is go to existing or stay new
					declare projectCursor cursor for 
					select max(startdate) startdate, max(createdate) createdate  from pr where billingclientid = @clientID and clBillingAddr = @currentAddress and wbs2 = '' and wbs3 = '' --will need to ask if this will be clientid or billlingclientid
					
					open projectCursor 
					fetch from projectCursor into @projectStartDate, @createDate
					
					close projectCursor
					deallocate projectCursor
				
					set @dateDifference = datediff(dd, @projectStartDate, @currentDate)
						
					if @dateDifference >= 365 
						update clAddress set AddressRelationship = 'Existing', DateEstablished = @currentDate where clientID = @clientID and Address = @currentAddress
						
					if @dateDifference < 365
						update clAddress set AddressRelationship = 'New', DateEstablished = @projectStartDate where clientID = @clientID and Address = @currentAddress
				
				
				end
				
				if @addressRelationship = 'Existing'
				begin
					--An existing address relationship can either go to 'Former' or go to 'New'
					declare projectCursor cursor for 
					select max(startdate) startdate, max(createdate) createdate  from pr where billingclientid = @clientID and clBillingAddr = @currentAddress and wbs2 = '' and wbs3 = '' --will need to ask if this will be clientid or billlingclientid
					
					open projectCursor 
					fetch from projectCursor into @projectStartDate, @createDate
					
					close projectCursor
					deallocate projectCursor
				
					set @dateDifference = datediff(dd, @projectStartDate, @currentDate)
						
					if @dateDifference >= 365 
						update clAddress set AddressRelationship = 'Former', DateEstablished = @currentDate where clientID = @clientID and Address = @currentAddress
						
					if @dateDifference < 365
						update clAddress set AddressRelationship = 'New', DateEstablished = @projectStartDate where clientID = @clientID and Address = @currentAddress
				end
				
				if @addressRelationship = 'Former'
				begin
					--An existing address relationship can either go to 'Former' or go to 'New'
					declare projectCursor cursor for 
					select max(startdate) startdate, max(createdate) createdate  from pr where billingclientid = @clientID and clBillingAddr = @currentAddress and wbs2 = '' and wbs3 = '' --will need to ask if this will be clientid or billlingclientid
					
					open projectCursor 
					fetch from projectCursor into @projectStartDate, @createDate
					
					close projectCursor
					deallocate projectCursor
				
					set @dateDifference = datediff(dd, @projectStartDate, @currentDate)
						
					if @dateDifference < 365
						update clAddress set AddressRelationship = 'New', DateEstablished = @projectStartDate where clientID = @clientID and Address = @currentAddress
				end
			fetch next from addressCursor Into @currentAddress	
		end -- end of while statement
		
		close addressCursor
		deallocate addressCursor
		
	end --end statement for sales account type
		end
	close tmpClientCursor
	deallocate tmpClientCursor
end