bfowler2
asked on
A cursor with the name 'tmpCursor' already exists.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ruscomp
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
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
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
From clAddress
Where ClientID = @clientID And Address = @currentAddress
open dateEstablishedCursor
fetch from dateEstablishedCursor Into @dateEstablished, @addressRelationship
close dateEstablishedCursor
deallocate dateEstablishedCursor
Into:
Select @dateEstablished=IsNull(da
From clAddress
Where ClientID = @clientID And Address = @currentAddress
Change this:
declare dateEstablishedCursor cursor
for Select IsNull(dateEstablished, '01/01/1901') dateEstablished, isNull(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(da
From clAddress
Where ClientID = @clientID And Address = @currentAddress
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...
Btw, I'm still looking for the loop issue...
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.
Add:
fetch next from tmpClientCursor into @clientID
...this should go right before the "end" keyword for the while loop.
ASKER
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
endfound 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 ?
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 :)
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
ASKER
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.
"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.
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.
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
Looks great... and combining it into the select should definitely be effecient.
Ta, just not quick enough... :)