Nested Loops on cursors
-
Dear All,
I was wondering if any can help me on this
I have the following structure: 2 cursors and I need to loop nested loops
While Cursor 1 has rows
Begin Loop 1
While Cursor 2 has rows
Begin Loop 2
End Loop 2
End Loop 1
The problem is I cannot use @@Fetch_Status because it is a global variable. Is there any mean of doing this loop.
Thank You
-
Yes, don't use a cursor...
What r u trying to accomplish in this task?.. maybe there's a set based solution for your problem.
-
The problem is I cannot use @@Fetch_Status... You can but as stated in BOL you have to be careful.
@@Fetch_Status contains the result of the last FETCH regardless of which cursor. All you have to do is get the logic right by only checking @@Fetch_Status after a FETCH.
Far away is close at hand in the images of elsewhere.
Anon. -
what I am tring to do is this
I have a main procedure with a cursor that has my master database table and I am looping on the records of this table (based on certain where condition) then inside this loop I am calling several sub procedures each has its own set of select cursors and update and delete statements and after returning from each sub procedures I run several statements in the main proc.
The logic goes as follows
Proc Main
while cursor 1 has records
Begin
call Proc sub 1 -- it has its own cursors on other tables
statement(s)
call Proc sub 2 -- it has its own cursors on other tables
statement(s)
End;
I am thinking of using a select statement to get number of records returned by each cursor and save the return value in a variable and loop on this variable but I am not sure if this solution is a neat way of looping on records.
-
Unless you are calling extremely complexe stored procs from within that 2nd cursor, I don't see why you could just make a series of inserts/deletes/udpates. Can we hear a little more about the task at hand?
-
Actually they are complex stored procedures. The procedures are used to calculate salaries in a payroll system.
I have to loop on employees to start generating their pay check. The main cursor loops on employees and then for each employee I call a procedure that calculates earnings and one that calculates taxes and another to calculate insurance and so on. Each has their own cursors and actually one or two of the procedures are recursive. The point is I know people who have implemented similar logic using Oracle PL/SQL. So I guess similar structure may exist in SQL server Transact SQL
-
Hi Gihan,
If you find you have no other options than to step through using a loop and nested loops. You are better off not using cursors.
Example of a cursorless loop:
DECLARE @Start1 int,
@End1 int
SET @Start1 = (SELECT MIN(PrimaryKeyValue) FROM TheTable)
SET @End1 = (SELECT MAX(PrimaryKeyValue) FROM TheTable)
WHILE @Start1 <= @End1
BEGIN
DO WhatEver - (call Proc sub 1 -- it has its own cursors on other tables)
SET @Start1 = (SELECT MIN(PrimaryKeyValue) FROM TheTable WHERE PrimaryKeyValue > @Start1)
END
This can be extended to run nested loops as:
DECLARE @Start1 int,
@End1 int,
@Start2 int,
@End2 int,
@Start3 int,
@End3 int
SET @Start1 = SELECT MIN(PrimaryKeyValue) FROM TheTable
SET @End1 = SELECT MAX(PrimaryKeyValue) FROM TheTable
WHILE @Start1 <= @End1
BEGIN
SET @Start2 = SELECT MIN(PrimaryKeyValue) FROM TheTable2
SET @End2 = SELECT MAX(PrimaryKeyValue) FROM TheTable2
WHILE @Start2 <= @End2
BEGIN
SET @Start3 = SELECT MIN(PrimaryKeyValue) FROM TheTable3
SET @End3 = SELECT MAX(PrimaryKeyValue) FROM TheTable3
WHILE @Start3 <= @End3
BEGIN
SET @Start3 = (SELECT MIN(PrimaryKeyValue) FROM TheTable3 WHERE PrimaryKeyValue > @Start3)
END
SET @Start2 = (SELECT MIN(PrimaryKeyValue) FROM TheTable2 WHERE PrimaryKeyValue > @Start2)
END
SET @Start1 = (SELECT MIN(PrimaryKeyValue) FROM TheTable WHERE PrimaryKeyValue > @Start1)
END
But remember - The good people in here will try to help you accomplish your goal without stepping through records for VERY good reason. Try to follow through with their suggestions BEFORE heading down this route.
Have Fun
Steve
We need men who can dream of things that never were.
-
I've once worked on a similar problem. I had to export the billing information to a text file so it could be imported to a 3rd party software.. Easy so far, but the tricky part was that I had to put all the items on the same line too. The final result looks something like this : (2 bills on 2 lines)
RQ0001,W05,01,"11",,FFCNUM="BETESTRIE.",FFLCNUM="BETESTRIE.",FFCOMM="19981",FFCOMCLI="ES056",FFTRANS="NET 30 JOURS",ITEM=".....................,04/08/2005 19981,Null,Null,Null,,,"",ITEM=".....................,INSTALLER VALVE ET AJUSTER LA CHAUDIERE.,Null,Null,Null,,,",ITEM="0,VALVE A L 'HUILE,1,679,679,31010,1,1",ITEM="10150,BALL JOINT 5/16'' FEMELLE,1,11.84,11.84,31010,1,1",ITEM="10150,BALL JOINT 5/16'' FEMELLE (ENVOYE PAR LA POSTE),1,11.84,11.84,31010,1,1",ITEM="TEMPS SIMPLE.........,Main d'oeuvre Dubé Richard,3,63,189,31030,1,1",ITEM="OUTILLAGE.......,Analyseur,1,30,30,31060,1,1",ITEM="DEPLACEMENT.....,DÉPLACEMENT,1,18,18,31040,1,1"#END#
RQ0002,W05,01,"11",,FFCNUM="CSHAUTCANT",FFLCNUM="ECO3CANTON",FFCOMM="19417",FFCOMCLI="3455",FFTRANS="NET 30 JOURS",ITEM=".....................,03/10/2005 19417,Null,Null,Null,,,"",ITEM=".....................,NETTOYER GICLEUR ET ELECTRODE. CHANGER ŒIL MAGIQUE,Null,Null,Null,,,"",ITEM=".....................,AJUSTEMENT DES FEUX.,Null,Null,Null,,,",ITEM="12797,CELLULE CADMIUM POUR C554,1,11.74,11.74,31010,1,1",ITEM="TEMPS SIMPLE.........,Main d'oeuvre Dubé Richard,2.50,63,157.50,31030,1,1",ITEM="OUTILLAGE.......,Analyseur,1,30,30,31060,1,1",ITEM="DEPLACEMENT.....,DÉPLACEMENT,1,102,102,31040,1,1"#END#
I had to create a bunch of parametered function (20) to extract all the little details and generate a single recordset will all the info required to export the bills, and delete them from the billing list. This system, however slow compared to a fully set based solution, can still create about 400 bills/second which is plenty since we do only about 400 bills per month. I think you'll have to create something like this in your case but it's gonna take a lot of work. That is if you wish to stay away from cursors.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply