Solved

Open Recordset in Stored Procedure and Loop Throgh It

Posted on 2007-11-30
4
1,878 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

914 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

19 Experts available now in Live!

Get 1:1 Help Now