recordset

Friends,

Is there any thing like a recordset concept in sql server,
where i could loop through and update each rows

I want to update a null column of a table with some values.

I already made a cursor(Shown below)

Instead of this can I make any recordset/resultset and update each rows?

Tks..

Declare @.RpID int
Declare @.JobID int
declare @.ID int
declare @.cnt int

set @.cnt = 0
set @.ID = 0

declare c1 cursor for

select Reprint_ID, Job_ID from ReprintInfo order by Job_ID
open c1
fetch next from c1 into @.RpID, @.JobID
while @.@.Fetch_Status = 0
begin

if (@.ID = @.JobID)
set @.cnt = @.cnt + 1
else
set @.cnt = 1

update reprintinfo set Reprintcount = @.cnt
where Reprint_ID = @.RpID

set @.ID = @.JobID

fetch next from c1 into @.RpID,@.JobID

end

close c1
deallocate c1

Thanks,

MG

A CURSOR is the closest thing to a 'recordset'. However, it is usually NOT the best way to do things in SQL Server. SQL Server handles SET based operations very efficiently.

It seems that you are consecutively numbering the rows for each JoB_ID. That can most likely be done using a SET based approach.

What version of SQL Server are you using?

|||

One way to perform the update if you have an index based on JOB_ID is to use the Transact SQL update extensions in something like:

create table dbo.reprintInfo
( Reprint_ID integer,
Job_ID integer,
reprintCount integer
)
go

create index reprintInfo_updNdx
on reprintInfo (job_id)
go

insert into reprintInfo
select 1, 1, null union all
select 2, 1, null union all
select 3, 2, null union all
select 4, 3, null union all
select 5, 3, null union all
select 6, 3, null union all
select 7, 3, null union all
select 8, 4, null
select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 NULL
-- 2 1 NULL
-- 3 2 NULL
-- 4 3 NULL
-- 5 3 NULL
-- 6 3 NULL
-- 7 3 NULL
-- 8 4 NULL

declare @.count integer
declare @.lastJobId integer
set @.count = 1

update reprintInfo
set reprintCount
= case when @.lastJobId = job_id
then @.count
else 1
end,
@.count = case when @.lastJobId is null
or @.lastJobId <> job_id
then 1
else @.count + 1
end,
@.lastJobId = job_Id
from reprintInfo (index=reprintInfo_updNdx)

select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 1
-- 2 1 2
-- 3 2 1
-- 4 3 1
-- 5 3 2
-- 6 3 3
-- 7 3 4
-- 8 4 1