Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Open Recordset in Stored Procedure and Loop Throgh It

Posted on 2007-11-30
4
Medium Priority
?
1,889 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 143

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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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