Solved

Open Recordset in Stored Procedure and Loop Throgh It

Posted on 2007-11-30
4
1,879 Views
Last Modified: 2008-02-01
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

0
Comment
Question by:TheUndecider
  • 3
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20385537
>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
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20385803
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
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20385805
of course

DateApproved = dateadd(d,datediff(day,0,getdate()),0)
0
 
LVL 6

Accepted Solution

by:
PaultheBroker earned 500 total points
ID: 20385809
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now