could you please post the create table statement.
BTW
IF EXISTS(SELECT * FROM tblBenefit WHERE acctid = @acctid AND visitNum =
@visitnum AND benefitType = 1 AND benefitCode = @tempMealCode1)
begin
update tblBenefit
set benefitCode=@tempMealCode1,
visitNum=@visitNum
where acctId=@acctid and sequence=1 and benefitType='ML'
end
ELSE
begin
insert into tblBenefit (acctId,visitNum,benefitType,sequence,
benefitCode)
VALUES(@acctid,@visitnum,'ML', 1, @tempMealCode1)
end
I wouldn't trust the @@ROWCOUNT value. Also I have had several SQLDBA that
this is more efficient.
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 20, 2002 3:54 PM
To: CF-Talk
Subject: Re: Annoying DB question
i do commit heres the transaction
CREATE PROCEDURE sp_newmeal (
@acctid varchar(20),
@visitnum int,
@delvisit int,
@tempMealCode1 varchar(6),
@tempMealCode2 varchar(6),
@tempMealCode3 varchar(6),
@tempMealCode4 varchar(6),
@tempMealCode5 varchar(6),
@tempMealCode6 varchar(6),
@tempMealCode7 varchar(6),
@tempMealCode8 varchar(6),
@tempMealCode9 varchar(6),
@tempMealCode10 varchar(6),
@tempMealCode11 varchar(6),
@tempMealCode12 varchar(6),
@tempMealCode13 varchar(6),
@tempMealCode14 varchar(6),
@tempMealCode15 varchar(6),
@tempMealCode16 varchar(6),
@tempMealCode17 varchar(6),
@tempMealCode18 varchar(6),
@tempMealCode19 varchar(6),
@tempMealCode20 varchar(6),
@tempMealCode21 varchar(6),
@tempMealCode22 varchar(6),
@tempMealCode23 varchar(6),
@tempMealCode24 varchar(6),
@tempMealCode25 varchar(6),
@tempMealCode26 varchar(6),
@tempMealCode27 varchar(6),
@tempMealCode28 varchar(6)
)
AS
begin
/* NOTES BY BILL W. Edited 4-26-2002
Removed orvisitnumber Because we just want to update the
old rows we'll update the new rows
or visitNum = @visitnum
*/
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
delete from tblBenefit
where acctid = @acctid
and (visitNum < @delvisit)
and benefitType = 'ML'
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode1,
visitNum=@visitNum
where acctId=@acctid and sequence=1 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 1, @tempMealCode1)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode2,
visitNum=@visitNum
where acctId=@acctid and sequence=2 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 2, @tempMealCode2)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode3,
visitNum=@visitNum
where acctId=@acctid and sequence=3 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 3, @tempMealCode3)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode4,
visitNum=@visitNum
where acctId=@acctid and sequence=4 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 4, @tempMealCode4)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode5,
visitNum=@visitNum
where acctId=@acctid and sequence=5 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 5, @tempMealCode5)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode6,
visitNum=@visitNum
where acctId=@acctid and sequence=6 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 6, @tempMealCode6)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode7,
visitNum=@visitNum
where acctId=@acctid and sequence=7 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 7, @tempMealCode7)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode8,
visitNum=@visitNum
where acctId=@acctid and sequence=8 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 8, @tempMealCode8)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode9,
visitNum=@visitNum
where acctId=@acctid and sequence=9 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 9, @tempMealCode9)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode10,
visitNum=@visitNum
where acctId=@acctid and sequence=10 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 10, @tempMealCode10)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode11,
visitNum=@visitNum
where acctId=@acctid and sequence=11 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 11, @tempMealCode11)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode12,
visitNum=@visitNum
where acctId=@acctid and sequence=12 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 12, @tempMealCode12)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode13,
visitNum=@visitNum
where acctId=@acctid and sequence=13 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 13, @tempMealCode13)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode14,
visitNum=@visitNum
where acctId=@acctid and sequence=14 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 14, @tempMealCode14)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode15,
visitNum=@visitNum
where acctId=@acctid and sequence=15 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 15, @tempMealCode15)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode16,
visitNum=@visitNum
where acctId=@acctid and sequence=16 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 16, @tempMealCode16)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode17,
visitNum=@visitNum
where acctId=@acctid and sequence=17 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 17, @tempMealCode17)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode18,
visitNum=@visitNum
where acctId=@acctid and sequence=18 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 18, @tempMealCode18)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode19,
visitNum=@visitNum
where acctId=@acctid and sequence=19 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 19, @tempMealCode19)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode20,
visitNum=@visitNum
where acctId=@acctid and sequence=20 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 20, @tempMealCode20)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode21,
visitNum=@visitNum
where acctId=@acctid and sequence=21 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 21, @tempMealCode21)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode22,
visitNum=@visitNum
where acctId=@acctid and sequence=22 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 22, @tempMealCode22)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode23,
visitNum=@visitNum
where acctId=@acctid and sequence=23 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 23, @tempMealCode23)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode24,
visitNum=@visitNum
where acctId=@acctid and sequence=24 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 24, @tempMealCode24)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode25,
visitNum=@visitNum
where acctId=@acctid and sequence=25 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 25, @tempMealCode25)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode26,
visitNum=@visitNum
where acctId=@acctid and sequence=26 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 26, @tempMealCode26)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode27,
visitNum=@visitNum
where acctId=@acctid and sequence=27 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 27, @tempMealCode27)
-------------------- END SEGMENT -------------------------------
-------------------- START SEGMENT -------------------------------
update tblBenefit
set benefitCode=@tempMealCode28,
visitNum=@visitNum
where acctId=@acctid and sequence=28 and benefitType='ML'
-- Lets Check to see if the update hit something if not insert!
IF @@ROWCOUNT = 0
insert into tblBenefit (acctId,visitNum,benefitType,sequence, benefitCode)
VALUES(@acctid,@visitnum,'ML', 28, @tempMealCode28)
-------------------- END SEGMENT -------------------------------
COMMIT TRANSACTION
end
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
