T-SQL Cursor within a cursor

[allenjasonbrown@gmail.com]

Hello All -


I have a T-SQL cursor that works great in SQL Server. Basically it will open a main cursor (cursor1) from tableA and then it opens a second cursor (cursor2) based on the same table (tableA) from the first cursor.

Basic psedo code:

open cusror1
 select name, id from tableA
where id >500
 order by id desc
    fetch into @name, @id

       open cursor2
          select name from table A
             where id < @id
             order by id desc

perform some stuff
 
        close cursor 2

fetch next cursor1
etc.

I need to convert to Access. Is this possible? Do I need to use recordsets? I am using Access 2007.

Thank you in advance!!

[orange]

Perhaps you could tell us what it is you are trying to do. And then someone could advise on what options may be appropriate.

[allenjasonbrown@gmail.com]

Basically I have one table with lets say 1000 rows. Each row has 4 important pieces of data......customerType, Customer_rating, Customer_distance, FLAG.

The first 200 rows (ordered by customer_rating) have the flag set to 'X'.

The first cursor, selects all the customers where the flag is null ordered by customer_rating desc. (So first time though row 201 would be selected for example).

Then with that customer, I perform another select on the same table where the customer_rating is greater than the rating for customer 201. (So this would return the first 200 rows).

Within the second cursor I perform some validation and might update the flag for 201 and then fetch the next record which would be 202 and perform the same inner select.

__________________________________
Right now I am thinking I could create two subs and have the first sub create my main cursor/recordset and then send the customer info (201) to a second sub where I can create another recordset??

[orange]

Great, but what I asking is along these lines, in order to let people know what you are trying to do.

I have different types of Customers. Some of them do x, and they get assigned a rating.
I want to process theCustomers with ratiings > abc ...

[pbaldy]

I think the answer to your question is yes, the Access equivalent would be 2 recordset loops.

I think where orange is heading is that there is often a more efficient way of accomplishing the same goal. Most SQL Server people I know basically frown on cursors, believing that there is usually a set-based way of accomplishing the task more efficiently. If you describe the process as orange is suggesting, someone may see a better way.

[allenjasonbrown@gmail.com]

Ok, thanks. Let me see if I can explain the process.

I have 1000 potential customers with a field where they have already been ranked from best potential to worst potential.....200 of them I already know are good. (Flag is set).

I want to read through table, grab the first one where the flag is not set, order by ranking (best first) (SAY THIS IS CUST201). Then take that customer(CUST201) and compare it to the 200 ones where they are already set as good. (This would be inner cursor).

Then i run a lot of field comparisons (not important for this) on:
CUST201 vs CUST1
CUST201 vs CUST2
CUST201 vs CUST3
CUST201 vs CUST4
etc....

If for example CUST201 vs CUST2 meets my criteria, then I will set CUST201 flag and exit inner cursor and then select next customer (CUST202). If I go through and compare all of them and none of them fit my criteria the FLAG does not get set.

Then compare CUST202 with 1-201 where FLAG is set.


Make any sense?

[orange]

I agree with Paul --- 2 recordset loops.
The purpose of my previous post was to get you to describe your "business need" in plain English. Just to tell people, who are there to advise and help, what your problem/opportunity is; and to remove/reduce "progammer talk".


Here's a link that may be useful. http://allenbrowne.com/ser-29.html
Good luck with your project.