Solved

A cursor with the name 'tmpCursor' already exists.

Posted on 2008-06-19
15
1,237 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
  • 8
  • 4
  • 3
15 Comments
 
LVL 7

Accepted Solution

by:
ruscomp earned 75 total points
Comment Utility
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
Comment Utility
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 50 total points
Comment Utility
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
 

Author Comment

by:bfowler2
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:bfowler2
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
"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
Comment Utility
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
Comment Utility
Looks great... and combining it into the select should definitely be effecient.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Ta, just not quick enough... :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now