squirleegirl
asked on
How to use multiple cursors to loop through tables in stored procedure
There is probably a very simple solution, but I just can't figure it out.
I've got 3 tables I am trying to query:
Table 1 has flat data
Table 2 has 8-10 records for each value in table 1
Table 3 has record for each table1/table2 value combination
I need to know the proper syntax for looping through the values of table 2 for each value of table 1. My select query works fine. The current stored proc I'm using is working per se, but it's not producing the correct results since its not looping correctly.
Does anyone have any examples of using multiple cursors to loop through table values? Any direction you can give would be appreciated.
Thanks!
I've got 3 tables I am trying to query:
Table 1 has flat data
Table 2 has 8-10 records for each value in table 1
Table 3 has record for each table1/table2 value combination
I need to know the proper syntax for looping through the values of table 2 for each value of table 1. My select query works fine. The current stored proc I'm using is working per se, but it's not producing the correct results since its not looping correctly.
Does anyone have any examples of using multiple cursors to loop through table values? Any direction you can give would be appreciated.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Actually, I just figured out a way to do this....Thanks JR2003 for responding so quickly
fyi, for future reference
/* sample data
create table table1 ( a1 int, b1 int )
insert into table1 select 1,2
insert into table1 select 2,3
insert into table1 select 4,5
create table table2 ( a2 int, b2 int )
insert into table2 select 1,13
insert into table2 select 1,14
insert into table2 select 1,15
insert into table2 select 4,20 */
declare @a1 int, @b1 int -- for table fields
declare @a2 int, @b2 int -- for table fields
declare c1 cursor for
select a1,b1 from table1
open c1
fetch next from c1 into @a1, @b1
while @@fetch_status = 0 begin
declare c2 cursor for
select a2, b2 from table2 where a2=@a1
open c2
fetch next from c2 into @a2, @b2
while @@fetch_status = 0 begin
select 'Table1 (a/b):', @a1, @b1, 'Table2 (a/b):', @a2, @b2
fetch next from c2 into @a2, @b2
end
close c2
deallocate c2
fetch next from c1 into @a1, @b1
end
close c1
deallocate c1
ASKER
The 2 cursor values are simply plugged into the where clause and it runs well and it runs well alone without the where. I'm just trying to figure an easy way to automate this query so it can be called by an onclick() thus the need for the stored proc.