November 19, 2013 at 1:29 pm
Hello all,
I have an enhancement request from business for them to be able to copy a cart.
This is an insurance application database used for testing the company's website, so a 'cart' is the collection of applicants (members) along with their demographic information, medical/dental insurance plans, etc.
It would take a lot of text to describe the entire table structure, but the short version is that the database is highly normalized.
A couple of the important tables:
Cart - the 'main' table... CartID is the primary key
Primary keys for all the tables are the table name + 'ID'
CartLocations - each cart may have multiple addresses for the members (mailing address might be different than the billing address) - CartID is a foreign key
CartMember - more than one member can be in a cart (primary, spouse, children) - CartID is a foreign key
CartPolicy - each cart can have more than one policy (normally there is only a medical policy, but sometimes a dental or vision plan is also added). - CartID is a foreign key
CartPolicyMember - this table links CartMember and CartPolicy - all members are part of the medical policies, but usually only the primary is added to the 2nd policy that's added. - foreign keys are CartMemberID and CartPolicyID
CartPolicyMemberRates - the individual member's rates for that particular policy - CartPolicyMemberID is the foreign key
moving on...
What I have now is a stored procedure with a couple dozen variables to store the new and old primary keys, and then groups of code like:
--Copy the Cart --- here's where @OUTNewCartID is set
insert into dbo.Cart ([CartCheckedOut],[CartExtractStatus],[CartIsActive],[IsShareable],[IsShared],[CartCreateDate],[CartCreatedBy],[CartModifyDate],[CartModifiedBy],[CancelReason], [CopiedFrom])
select [CartCheckedOut],'Not Ready',[CartIsActive],[IsShareable],[IsShared],DATEADD(hour, -1, GETDATE()),@UserName,DATEADD(hour, -1, GETDATE()),@UserName,'',@CartID
from dbo.Cart
where CartID = @CartID
Select @OUTNewCartID = SCOPE_IDENTITY()
@CartID is one of the parameters that's passed to the SP.
Then, I have (after declaring @OldCartMemberID_Primary):
SET @OldCartMemberID_Primary =(SELECT CartMemberID FROM CartMember where CartID = @CartID AND PersonPosition = 1)
IF @OldCartMemberID_Primary IS NOT NULL
BEGIN
SELECT @OriginalMembersID=m.OriginalMembersID, @MembersID=m.MembersID, @RateClass=m.RateClass, @FirstName=m.FirstName, @MiddleInitial=m.MiddleInitial, @LastName= om.LastName + @TextToAppendToName, @Gender=m.Gender, @Smoker=m.Smoker, @Height=m.Height, @Weight=m.Weight, @DOB= om.DOB + @NumberOfCopies, @StateofBirth=m.StateOfBirth, @FTStudent=m.FTStudent, @ApplicationOwner=m.ApplicationOwner, @MembersIsActive=m.MembersIsActive, @Surname=m.Surname, @PersonPosition=cm.PersonPosition, @PersonCode=cm.PersonCode, @RelationshipToInsured=cm.RelationshipToInsured, @CartMemberIsActive=cm.CartMemberIsActive
FROM CartMember cm
INNER JOIN CartMemberAssociation cma ON cm.CartMemberID=cma.CartMemberID
INNER JOIN Members m ON cma.MembersID=m.MembersID
INNER JOIN OriginalMembers om on om.OriginalMembersID = m.OriginalMembersID
WHERE cm.CartMemberID = @OldCartMemberID_Primary
I just keep going like that, grabbing new primary keys when necessary, and using them along with INSERTs to copy the data into the new records.
The stored procedure works, but recently, there's been a couple fields added to the CartMember table, and also "stand alone" Vision products were added, which means I'll have to add a few hundred lines of code (if I keep the SP in its current form).
The entire stored procedure is really really long, so I've included it in an attached text file.
The whole thing is done in a very brute force sort of way, and I wonder if there's a more elegant solution.
Any help that you all can provide will be greatly appreciated.
November 20, 2013 at 4:56 am
There are definitely ways to speed this process up (or at least make the code more compressed).
But you're not seriously asking in a web forum if someone could tune your 2000 lines of code, are you??
In general, I would start with rethinking the whole process as not dealing with a bunch of single attributes (=variables) but rather with rows and columns.
Just a basic example:
IF @NewCartPolicyID_Medical IS NOT NULL
BEGIN
insert into dbo.CartPolicyIdentifiers ([CartPolicyID],[IdentifierType],[Identifier],[CartPolicyIdentifiersIsActive],[CartPolicyIdentifiersCreateDate],[CartPolicyIdentifiersCreatedBy],[CartPolicyIdentifiersModifyDate],[CartPolicyIdentifiersModifiedBy])
select @NewCartPolicyID_Medical,[IdentifierType],[Identifier],[CartPolicyIdentifiersIsActive],DATEADD(hour, -1, GETDATE()),@UserName,DATEADD(hour, -1, GETDATE()),@UserName
from dbo.CartPolicyIdentifiers cpi
INNER JOIN CartPolicy cp on cp.CartPolicyID = cpi.CartPolicyID
where cp.CartPolicyID = @OldCartPolicyID_Medical
END
--Copy the CartPolicyIdentifiers - Dental
IF @NewCartPolicyID_Dental IS NOT NULL
BEGIN
insert into dbo.CartPolicyIdentifiers ([CartPolicyID],[IdentifierType],[Identifier],[CartPolicyIdentifiersIsActive],[CartPolicyIdentifiersCreateDate],[CartPolicyIdentifiersCreatedBy],[CartPolicyIdentifiersModifyDate],[CartPolicyIdentifiersModifiedBy])
select @NewCartPolicyID_Dental,[IdentifierType],[Identifier],[CartPolicyIdentifiersIsActive],DATEADD(hour, -1, GETDATE()),@UserName,DATEADD(hour, -1, GETDATE()),@UserName
from dbo.CartPolicyIdentifiers cpi
INNER JOIN CartPolicy cp on cp.CartPolicyID = cpi.CartPolicyID
where cp.CartPolicyID = @OldCartPolicyID_Dental
END
coud be replaced with
insert into dbo.CartPolicyIdentifiers ([CartPolicyID],[IdentifierType],[Identifier],[CartPolicyIdentifiersIsActive],[CartPolicyIdentifiersCreateDate],[CartPolicyIdentifiersCreatedBy],[CartPolicyIdentifiersModifyDate],[CartPolicyIdentifiersModifiedBy])
select t.NewCartPolicyID,[IdentifierType],[Identifier],[CartPolicyIdentifiersIsActive],DATEADD(hour, -1, GETDATE()),UserName,DATEADD(hour, -1, GETDATE()),@UserName
from dbo.CartPolicyIdentifiers cpi
INNER JOIN CartPolicy cp on cp.CartPolicyID = cpi.CartPolicyID
INNER JOIN #VariableCollector t ON cp.CartPolicyID = t.OldCartPolicyID
WHERE t.NewCartPolicyID IS NOT NULL
--The referenced #VariableCollector could look like
CREATE TABLE #VariableCollector
(
ID INT PRIMARY KEY IDENTITY(1,1),
PolicyType, -- Medical or Dental
OldCartPolicyID,
NewCartPolicyID,
UserName,
...
)
After all, this whole process for sure can be compressed tremendously. But there are people out there making a living out of such time-consuming requirements...
So you might want to look for someone to hire for the tuning process.
From my point of view the subject exceeds the purpose of a forum question.
November 20, 2013 at 6:27 am
LutzM (11/20/2013)
There are definitely ways to speed this process up (or at least make the code more compressed).But you're not seriously asking in a web forum if someone could tune your 2000 lines of code, are you??
Definitely not; just looking for some direction...
LutzM (11/20/2013)
After all, this whole process for sure can be compressed tremendously.....
That's the sort of thing I was looking for; thank you very much.
LutzM (11/20/2013)
From my point of view the subject exceeds the purpose of a forum question.
I worried about that a bit; I had a sense, though, that it could be made shorter, I was just having a difficult time coming up with an effective way.
Again, I very much appreciate the help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply