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
'Do nothing because the fields in the second table are not good to go
'Don't even bother querying Table2 as the record is not good go to.
MyRecordSet.MoveNext ' move to the next record in the recordset and loop.
Thanks for all your help.