Nested Cursor example

Question

Post reply

Nested Cursor example

gopi6417

Old Hand

Points: 306
More actions
February 25, 2015 at 5:14 am

#314965

select * from emp
select * from dept
select a.empno,a.ename,job,sal,b.deptno,b.dname
from emp a
join dept b
on a.deptno=b.deptno
where a.deptno=10
Declare
@Empno int,@Ename nvarchar(20),@Job nvarchar(20),@Sal decimal(10,2),@Deptno int,@DeptName nvarchar(15)
------------------------------------------------------cursor1
declare Dept_Cur cursor for
select deptno,dname from dept
open Dept_Cur
fetch next from Dept_Cur into @Deptno,@DeptName
while @@Fetch_status=0
begin
-------------------------------------------------------cursor2
declare Emp_Cur cursor for
select a.empno,a.ename,job,sal
from emp a
join dept b
on a.deptno=b.deptno
where a.deptno=@Deptno
open Emp_Cur
fetch next from Emp_cur into @Empno,@Ename,@Job,@Sal
while @@fetch_status=0
begin
print
cast(@Deptno as varchar(10)) + ' ' +
@DeptName + ' ' +
cast(@Empno as varchar(10)) + ' ' +
@Ename + ' ' + @Job + ' '+ cast(@Sal as varchar(10))
fetch next from Emp_Cur into @Empno,@Ename,@Job,@Sal
end
close Emp_Cur
deallocate Emp_cur
-----------------------------------------------------------cursor2
fetch next from Dept_Cur into @Deptno,@DeptName
End
close Dept_Cur
deallocate Dept_Cur
-------------------------------------------------------cursor1

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Eirikur Eiriksson

SSC Guru

Points: 182882

February 25, 2015 at 5:20 am

#1779856

Quick question, what is the question?

😎

Ed Wagner SSC Guru Points: 287024 More actions · Answer 2

Eirikur Eiriksson (2/25/2015)
Quick question, what is the question?
😎

Right. Without an actual question, it's difficult to give an actual answer. 😉 I don't like the cursors. Why not just fire a SELECT statement instead of using nested RBAR?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

vecerda SSCrazy Points: 2532 More actions · Answer 3

vecerda

SSCrazy

Points: 2532

February 26, 2015 at 1:54 pm

#1780400

@Eirikur Eiriksson LOL, this was a good one!

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

There's no question here. The third select on the original post shows the set-based replacement for the cursors. The title truly reflects what was posted... it's an example. Cool, huh?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

Jeff Moden (2/26/2015)
There's no question here. The third select on the original post shows the set-based replacement for the cursors. The title truly reflects what was posted... it's an example. Cool, huh?

Totally missed that, cannot even use my normal lack of industrial strength espresso excuse:-D

😎

Ed Wagner SSC Guru Points: 287024 More actions · Answer 6

Eirikur Eiriksson (2/27/2015)
Jeff Moden (2/26/2015)
There's no question here. The third select on the original post shows the set-based replacement for the cursors. The title truly reflects what was posted... it's an example. Cool, huh?
Totally missed that, cannot even use my normal lack of industrial strength espresso excuse:-D
😎

That's why I asked about firing a single SELECT statement instad. The nested RBAR would be even worse than simple RBAR.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 7

@gopi6417,

This would make a great article. You should write it up and submit it to the folks on this site.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)