SQL ServerTSQLSyntax

SQL - TSQL - Cursors

Basic Cursor Sample

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
declare aCursor cursor for
select lName from x where lName='b%'

open aCursor

fetch next from aCursor into @lName

while @@Fetch_Status=0
begin

  fetch next from aCursor into @lName

end

close aCursor
deallocate aCursor

Notes

Warning, I had a problem with in clause in a select statement. I was getting a lockup on the fetch statement. When I changed

1
2
3
4
5
6
7
8
9
declare mcursor cursor 
for
 select usnode, linksuffix, dsnode 
 from FlowPipe with (NOLOCK)
 where DSNode in 
  (select distinct USNode 
   from FlowPipeTrace 
   where TraceID=0 --(@traceid-1)
  )

to

1
2
3
4
5
6
declare mcursor cursor 
for
 select fp.usnode, fp.linksuffix, fp.dsnode 
 from FlowPipe fp
 left join FlowPipeTrace as fpt on fp.DSNode=fpt.usnode
 where fpt.TraceID=(@traceid-1)

Things started working as I expected.