Open Recordset in Stored Procedure and Loop Throgh It

Hello,

I'm trying to convert some code I did in Access 2000 into a SQL Stored procedure.   I am using SQL Server 2000. Basically, what I need to do is to loop through a recordset (from Table 1) and check some field values for each record.

If the checked values are good to go, I need to do another query using the current record's SSN to get other fields pertaining to this SSN from Table2.  If these second field values are also good to go, I finally can update the record in Table1.

I think this could be easily done with cursors.  However, I'm not supposed to use them.

Here's a rough version of what I'm trying to accomplish:

Query all records in table1 added today and store them in MyRecordSet.
(Select SSN, Status, Code1 from Table1 Where DateAdded = Today's Date)

'start the loop
Do Until MyRecordSet = EOF

MyRecordSet.MoveFirst ' go to the first record in the recordset

  ' for the current record
      'Check the Status Field (should be = 'OK')
                 'Check the Code1 field (should be = '501')

      IF Status = 'OK' and Code1 = '501 THEN
            'Query Table2 using current SSN
            (Select SSN, State from Table2 Where SSN = CurrentSSN)
            'Check the State field (should be = 'NY')

            IF State = 'NY' Then
                  'Update Table1 because the record is good to go
                  UPDATE Table1 SET ApprovalStatus = 'Approved', DateApproved = 'Today' WHERE SSN = CurrentSSN
            ELSE
                  'Do nothing because the fields in the second table are not good to go
            END IF
      ELSE
            'Don't even bother querying Table2 as the record is not good go to.
      END IF

MyRecordSet.MoveNext ' move to the next record in the recordset and loop.

LOOP

________________________________________

Thanks for all your help.


Open in new window

TheUndeciderAsked:
Who is Participating?
 
PaultheBrokerCommented:
I interpreted the logic as being:
In order for you to Approve this record, then
it must have been added today,
it must have a status of OK
it must have a code of 501
it must be in NY
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>MyRecordSet.MoveFirst
that line is usually unnecessary, and is especially at  the wrong place, because if you put it inside the "while not myrecordset.eof", you will "loop" all the time on record #1 over and over again...
0
 
PaultheBrokerCommented:
hmmm - ok picking out the logic from your psuedo code was kinda tricky, but maybe this would do it (and BTW, whoever banned you from using a cursor should get a medal....)
UPDATE table1
SET ApprovalStatus = 'Approved', DateApproved = 'Today'
FROM table1 t1
where DateAdded = dateadd(d,datediff(day,0,getdate()),0) --today's date
AND status = 'OK'
and Code1 = '501'
and SSN IN (select SSN from table2 t2 where t1.ssn = t2.ssn and State = 'NY')

Open in new window

0
 
PaultheBrokerCommented:
of course

DateApproved = dateadd(d,datediff(day,0,getdate()),0)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.