Solved

Open Recordset in Stored Procedure and Loop Throgh It

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

734 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