January 11, 2010 at 10:25 am
Before I get inundated by cursor haters, if there's a better way to populate these variables, fine. I just need to get over this hurdle. The first row in the result set is always skipped and I don't know why. The result set should be :
10.68
30.53
50.4
I only get rows 2 and 3. Here is my code, please be gentle:
DECLARE @emerRate int,
@hospRate int,
@majinjuryRate int,
@totalBaseRate int,
@tmpRate int,
@baseModID int
DECLARE RATES CURSOR STATIC LOCAL
FOR
select prd_WPABaseModule.BaseModID, prd_WPABaseModuleRate.BaseRate
from ent_State
inner join prd_State_WPABaseModule on ent_State.StateID =
prd_State_WPABaseModule.fk_StateID
inner join prd_WPABaseModule on prd_State_WPABaseModule.fk_BaseModID =
prd_WPABaseModule.BaseModID
left outer join prd_WPABaseModuleRate on prd_WPABaseModule.BaseModID =
prd_WPABaseModuleRate.fk_BaseModID
where prd_WPABaseModule.fk_RiderValue = 31 and
prd_WPABaseModuleRate.fk_EligibleMemberID = 1 and ent_State.StateID = 13
OPEN RATES
FETCH RATES INTO @baseModID, @tmpRate
while @@fetch_status <> -1
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
--if @baseModID = 1
print @baseModID
print @tmpRate
FETCH NEXT FROM RATES
END
END
CLOSE RATES
DEALLOCATE RATES
January 11, 2010 at 10:33 am
I'm used to cursors working with "while @@fetch_status = 0" instead of the nested version you have.
What is it you're actually doing with the cursor? Most likely, you're going to be better off getting rid of it, rather than fixing it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 11, 2010 at 11:01 am
I'm just trying to populate the vars
@emerRate int,
@hospRate int,
@majinjuryRate int
With the BaseRate values in the result set.
January 12, 2010 at 12:53 am
Dont you think you should be giving the variable names in your subsequent fetch also?
FETCH RATES INTO @baseModID, @tmpRate
(within your while loop)
---------------------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply