Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to use multiple cursors to loop through tables in stored procedure

Posted on 2007-11-20
4
Medium Priority
?
11,889 Views
Last Modified: 2010-04-28
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!
0
Comment
Question by:squirleegirl
[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
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
JR2003 earned 150 total points
ID: 20321996
You should really just use one cursor on a query with joins to all the tables as this is more efficient:


SELECT *
  FROM Table1 T1
  LEFT JOIN Table2 T2
         ON T1.Table1Id = T2.Table1Id 
  LEFT JOIN Table3 T3
         ON T2.Table2Id = T3.Table2Id 
 ORDER BY T1.Table1Id, T2.Table2Id, T3.Table3Id

Open in new window

0
 

Author Comment

by:squirleegirl
ID: 20322037
Thanks JR2003, fortunately, the SELECT query works really well.

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.
0
 

Author Comment

by:squirleegirl
ID: 20322227
Actually, I just figured out a way to do this....Thanks JR2003 for responding so quickly
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20322341
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

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

618 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