Solved

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

Posted on 2007-11-20
4
11,663 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
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
JR2003 earned 50 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

948 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now