Looping through a table... 1

[Snaggs]

I have a table that has a birthday in it along with a Stage field. Birthdate is a DateTime field and the Stage is a VarChar(50).

I put the Birthday in the table first, then I want to determine what stage the user is in. I have a function that looks up the stage based on how old the person is. For example:

Stage = Infant if < 1 year
Stage = Baby if > 1 year < and less 2 years
Stage = Child if > 2 years and < 12 years
Stage = Teenager if > 12 years and < 16 year
etc, etc...

The stored proc that returns the stage uses an out parameter and it's working just fine.

What I want to do is loop through each record in the table and update the Stage field for each record based on the birthday.

In ADO/VB I want to do this:

rs.Open "Person"

Do Until rs.EOF
   rs!Stage = GetStage(rs!Birthday)
   rs.Update
   rs.MoveNext
Loop

rs.Close

Here's the code I have so far but it's not updating the Stage field.

/* Convert to Stage */
Declare tAvail Cursor For
SELECT GroupBirthdate, DateIn, Stage FROM tAvailable

OPEN tAvail

/* Determine and update the Stage */
FETCH NEXT FROM tAvail Into @GroupBirthDate, @DateIn, @Stage
WHILE @@FETCH_STATUS = 0
BEGIN
    /* Get the stage based on the datein and the birthdate */
    exec sp_GetStage @GroupBirthDate, @DateIn, @Stage out

    I think I need something here to do the update.  But what?

    FETCH NEXT FROM tAvail Into @GroupBirthDate, @DateIn, @Stage
END

CLOSE tAvail
DEALLOCATE tAvail
GO

I'm not a T-SQL expert as you can probably tell. Thanks for looking and for any help you can provide.

Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.

 

[wg26]

I think you have a problem in your cursor declaration. In order to update your date, you either have to declare your cursor as Keyset Cursor if you have unique index or as dynamic Cursor...
Such as &quot;Delare tAvail Cursor Keyset For&quot;
or &quot;Delare tAvail Cursor Dynamic For&quot; then you can update your data...Hope it helps...

 

[wsmall73]

Why not use some simply updates

update Table Set status=
case birthday
WHEN < 1 year THEN 'Infant'
WHEN >1 and < 2 THEN 'Baby'

ETC???
Maybe someone else has some ideas on this.

and going forward as part of your insert/update statement
use the same case to insert the status as you insert the case birthday??

 

[Snaggs]

wg26,

Thanks for the response. I don't have a unique key for each record, so I think I have to use dynamic. However it's still not updating the Stage field after I declared it as Dynamic.

I have this now:

/* Convert to Stage */
Declare tAvail Cursor Dynamic For
SELECT GroupBirthdate, DateIn, Stage FROM tAvailable

I understand the when I use the Fetch statement I can fetch fields into variables. My question is, if I update those variables, do they automatically update the recordset they came from or is there some command I have to issue so the data in the table gets updated?

Can I just say

@Stage = <some value>

and have it update the data in the table? Or do I have to do something else like an Update command or something?

Thanks, Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.

 

[wg26]

Try this and see if it works...

DECLARE your cursor
SELECT column
FROM thetable

DECLARE holding varibles
Open yourcursor
Fetch Next from yourcursor into @varibles

while @@Fetch_status =0
begin
if(Datediff(year, brithdate,whateverDateyouruse)<1)
begin
update table
set stage=&quot;Infant&quot;
Where Current of yourcursor
end
else if(Datediff()<2)
begin
update table
set stage=&quot;baby&quot;
Where Current of yourcursor
end
...and so on
End
Fetch Next from yourcurson into @varibles
End

close yourcursor
deallocate yourcursor

Hope it helps...
Correct me if I am wrong, anyone, please!