Does anyone have an example of a scrollable, update cursor?

[majorbroncosfan]

I've been trying to get the hang of cursors. I know very well how to iterate through a recordset in programming languages, but am trying to do it via stored procedures in SQL Server. Does anybody have an example of how to scroll through a table and update a column based on a value in another table?

 

[aalmeida]

I guess this will help you

Create Table #A1(N_Id int, F_Name varchar(15))
Insert INTO #A1 VALUES ( 1, 'None')
Insert INTO #A1 VALUES ( 2, 'None')
Insert INTO #A1 VALUES ( 3, 'None')
Insert INTO #A1 VALUES ( 4, 'None')
Create table #B1 (N_Id int, F_Name varchar(15))
Insert INTO #B1 VALUES (1, 'Ana')
Insert INTO #B1 VALUES ( 2, 'Nicole')
Insert INTO #B1 VALUES ( 3, 'Barbara')
Insert INTO #B1 VALUES ( 4, 'Sandra')

Select * from #A1

Update #A1 set F_name = (Select F_Name From #B1 Where #A1.N_Id = #B1.N_Id)
FROM #B1

Select * from #A1

DROP TABLE #A1
DROP TABLE #B1 AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[Phwrd]

set nocount on
declare
@long varchar (75),
@short varchar (25),
@testid varchar (10),
@alt varchar (25),
@cpt varchar (25),
@hisid varchar (25),
@anal varchar (25),
@tdtestid varchar (25),
@row int,
@order varchar (3)

declare abc cursor for
select longname, shortname, testid, alttestid,cpt,
hisid,analyzer,testdefstestid,row,orderable
from reflabtestdefs where analyzer not like 'spec%'

set @row=(select max(row) from reflabtestdefs)+1
set @order='Yes'

open abc
while 1=1
begin
set @row=@row+1
fetch abc into long,@short,@testid,@alt,@cpt,
@hisid,@anal,@tdtestid,@row,@order
if @@fetch_status<>0
break
insert into reflabtestdefs (longname,shortname,testid,alttestid,cpt,hisid,analyzer,testdefstestid,row,orderable)
values
(@long,@short,@testid,@alt,@cpt,@hisid,@anal,@tdtestid,@row,@order)
end
close abc
deallocate abc

Try this...
Pat

 

[majorbroncosfan]

Thank you SO much!!!!

IT WORKS!!!!