Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

A cursor with the name 'tmpCursor' already exists.

Posted on 2008-06-19
15
Medium Priority
?
1,254 Views
Last Modified: 2008-06-19
I am trying to modify a stored procedure that originally passed in a variable from an IIS application.  Instead of passing in a client ID, I want to cycle through all client ID and then make updates based on different criteria.  The challenge that I have is that I receive "A cursor with the name 'tmpCursor' already exists" when I try to run the stored procedure and it creates an infinite loop between @@fetch_staus and declaring the temp  cursor.  I have also tried moving the start of the while statement before the declare tmpClientCursor... line, but another retrieving antother fetch_status further down in the code changes the value to a -1 and the while exits to early.  Any ideas on how to use two while statements and have the script cycle through both?
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

Open in new window

0
Comment
Question by:bfowler2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
15 Comments
 
LVL 7

Accepted Solution

by:
ruscomp earned 300 total points
ID: 21823491
Why not change:
declare tmpCursor cursor for
            select custSalesAccountType from clientCustomTabFields Where clientID = @clientID
                  
      open tmpCursor
      fetch from tmpCursor into @salesAccountType
      
      close tmpCursor
      deallocate tmpCursor

Into:
select @salesAccountType=custSalesAccountType from clientCustomTabFields Where clientID = @clientID
0
 
LVL 7

Expert Comment

by:ruscomp
ID: 21823511
It seems like you are opening the cursor just to set the value of "@salesAccountType" to the "custSalesAccountType", if this is so, it can all be done in one statement, without using a cursor, as I have shown above.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 200 total points
ID: 21823893
It does seem to me that you are using cursors in some circumstances where you do not need to. Is there any particular reason for this ?

Would you like to trim out the cursors where possible ?


e.g. I tend to use set for setting variable, and select when extracting data, so in the case above for tmpcursor, I would have used :

      set @salesAccountType = (select custSalesAccountType from clientCustomTabFields Where clientID = @clientID)

with the understanding that there was only 1, otherwise would need to use additional qualifiers... Can see the same scenarios repeated throughout.

I am a fan of cursors - you will see varying comments about them, but they do need to be used in the most appropriate way, and are really a pointer the the current record of a rowset. If they are not, then there is a very high possibility that a cusor is not quite the best option.

I know that doesn't really answer why the error, except it kind of does - by avoiding the use if not needed, then it avoids the error. Do you want to pursue further ?

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:bfowler2
ID: 21824289
I do appreciate the comments and it does simplify the script quite a bit.  I am mostly using cursors from my lack of understanding the differnece between a cursor and a variable.

I did make the changes but I still loop in the same spot:


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 */
	select @salesAccountType=custSalesAccountType from clientCustomTabFields Where clientID = @clientID
	
	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

Open in new window

0
 
LVL 7

Expert Comment

by:ruscomp
ID: 21824394
Still looking through to see the loop issue... and came across this.

Change this:
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

Into:
Select  @dateEstablished=IsNull(dateEstablished, '01/01/1901'), @addressRelationship=isNull(addressRelationship, '')
                  From    clAddress
                  Where   ClientID = @clientID And Address = @currentAddress
0
 
LVL 7

Expert Comment

by:ruscomp
ID: 21824427
I noticed several more places where cursors are used similarly to the 2 areas I've gone through already.... Not saying that you have to change them, but I definitely think it will help to tidy up the code (and a lighter process load for the server). If you see from the 2 examples above you can convert them yourself, or I can do it for you...

Btw, I'm still looking for the loop issue...
0
 
LVL 7

Expert Comment

by:ruscomp
ID: 21824449
I beleive the loop is going forever because there is no "fetch next" statement for "tmpClientCursor"....

Add:
 fetch next from tmpClientCursor into @clientID

...this should go right before the "end" keyword for the while loop.
0
 

Author Comment

by:bfowler2
ID: 21824701
Found it.  added begin statement right after while statement.  I read somewhere that you want to add a begin statement after any while or if statement.   I appreciate the education on cursors and I don't think I would have solved it without additional help.  Do you see any other suggestions with the script, now?
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()
	 
	declare tmpClientCursor cursor for 
		select clientid from claddress
	
	open tmpClientCursor
	fetch from tmpClientCursor into @clientID
	while @@fetch_status = 0
	begin
	
	select @salesAccountType=custSalesAccountType from clientCustomTabFields Where clientID = @clientID
	
	if @salesAccountType = 'Regular'
	begin
		--loop through all of the addresses for the current client id
		
	set @currentAddress = (select address from clAddress where ClientID = @clientID)
	
		
		while @@fetch_status = 0
		begin
						
		Select @dateestablished=isnull(dateEstablished, '01/01/1901'), @addressRelationship=isNull(addressRelationship,'')
				from ClAddress
				Where ClientID = @clientID and Address = @currentAddress
				--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
	fetch next from tmpClientCursor into @clientID
	end
	close tmpClientCursor
	deallocate tmpClientCursor
end

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21824766

found that one, and a few others - you guys are changing too fast - I was trying to type the thing up...

you now have lines 40 and 41 out of place,

need to do to lines 60:67 pretty much what you did in lines 43:45

you are getting there - do you want my typing ?
0
 
LVL 7

Expert Comment

by:ruscomp
ID: 21824771
I've changed all the code that uses a cursor to set the variable so that it directly sets the variable instead. This should be more efficient than using cursors, and also helps to make the code more readable and easier to understand (at least in my opinion).

Glad that I could help :)
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()
	 
	declare tmpClientCursor cursor for 
		select clientid from claddress
	
	open tmpClientCursor
	fetch from tmpClientCursor into @clientID
	while @@fetch_status = 0
	begin
	
	select @salesAccountType=custSalesAccountType from clientCustomTabFields Where clientID = @clientID
	
	if @salesAccountType = 'Regular'
	begin
		--loop through all of the addresses for the current client id
		
	set @currentAddress = (select address from clAddress where ClientID = @clientID)
	
		
		while @@fetch_status = 0
		begin
						
		Select @dateestablished=isnull(dateEstablished, '01/01/1901'), @addressRelationship=isNull(addressRelationship,'')
				from ClAddress
				Where ClientID = @clientID and Address = @currentAddress
				--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
					select @projectStartDate=max(startdate), @createDate=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
			
					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
					select @projectStartDate=max(startdate), @createDate=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
			
					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
					select @projectStartDate=max(startdate), @createDate=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
				
					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'
					select @projectStartDate=max(startdate), @createDate=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
				
					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'
					select @projectStartDate=max(startdate), @createDate=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
				
					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
	fetch next from tmpClientCursor into @clientID
	end
	close tmpClientCursor
	deallocate tmpClientCursor
end

Open in new window

0
 

Author Comment

by:bfowler2
ID: 21824911
I wouldn't mind taking a look at both codes.  To answer the question on why so many cursors were used.  I believe they were originally done as cursors because this was set up as a trigger for an event that occurred several times a day on one particular client ID.  Apparently cursors can be more efficient.  My goal was to modify so that it could be run across all client ID's on a monthly basis.  I do think that removing the cursors greatly simplifies it.
0
 
LVL 7

Expert Comment

by:ruscomp
ID: 21824980
"Apparently cursors can be more efficient". In most cases cursors are extremely efficient... but I do beleive that they are meant to be used in a table-like manner (record-by-record, like in your loops), not just for setting a variable. I imagine that a cursor would have more overhead for the server to allocate space for the cursor, and then close it, versus setting a variable that already has it's space allocated.

I'm not sure how much data you are dealing with, but obviously, with small amounts of the data, the difference will be un-noticable (thanks to the amazing processing power of SQL Server)

I'd be glad to help with any more issues you have with your procedure... If so, you may want to post a new question though so others can see it and help as well.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21825259
OK, I took a slightly different approach... I looked at the cursors and combined some of the variable settings where appropriate - ie where getting from claddress. Also comined picking up the "regular" into the select ...


ALTER procedure [dbo].[sp_ues_workflow_one_update_address_relationship]  --(@clientid varchar(32))
as
begin
 
    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
 
	set @currentDate = getDate()
	 
	declare tmpClientCursor cursor for select clientid,currentaddress,isnull(dateEstablished, '01/01/1901'),isNull(addressRelationship,''),c.custSalesAccountType
                                       from claddress 
                                       inner join clientCustomTabFields c on c.clientid = claddress.clientid
                                       where c.custSalesAccountType = 'Regular'
	
	open tmpClientCursor
	fetch next from tmpClientCursor into @clientID,@currentaddress,@dateestablished,@addressRelationship,@salesAccountType
	while @@fetch_status = 0
	begin
	
					
			--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
				select @projectStartDate=max(startdate), @createDate=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
		
				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
				select @projectStartDate=max(startdate), @createDate=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
		
				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
				select @projectStartDate=max(startdate), @createDate=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
			
				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'
				select @projectStartDate=max(startdate), @createDate=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
			
				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'
				select @projectStartDate=max(startdate), @createDate=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
			
				set @dateDifference = datediff(dd, @projectStartDate, @currentDate)
					
				if @dateDifference < 365
					update clAddress set AddressRelationship = 'New', DateEstablished = @projectStartDate where clientID = @clientID and Address = @currentAddress
			end
 
		end -- end of while statement
	
 		fetch next from tmpClientCursor into @clientID,@currentaddress,@dateestablished,@addressRelationship,@salesAccountType
	end
	close tmpClientCursor
	deallocate tmpClientCursor
end

Open in new window

0
 
LVL 7

Expert Comment

by:ruscomp
ID: 21825295
Looks great... and combining it into the select should definitely be effecient.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21825402
Ta, just not quick enough... :)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question