recordset - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

recordset

Author

Topic

mejogeorge
Starting Member

2 Posts

Posted - 2007-03-27 : 15:37:03

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

Thanks,
MG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-27 : 15:39:26

Yes. You can use a WHILE loop or a cursor.

But you shouldn't use these unless absolutely necessary as processing data one row at a time is slow in SQL Server.

Could you show us an example of what you want to do?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

mejogeorge
Starting Member

2 Posts

Posted - 2007-03-27 : 15:46:03

Thanks Tara Kizer,

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-27 : 16:05:31

Please show us a data example of what you want to do.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-27 : 16:18:10

Also, what version of SQL Server are you using?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-27 : 16:43:59

Just as a shot in the dark, here is an update that is similar to what your cursor does. The update assumes some sort of order on the ReprintID.

-- Setup
DECLARE @ReprintInfo TABLE(JobID INT, ReprintID INT, ReprintCount INT)

INSERT @ReprintInfo
SELECT 1, 4, 0 UNION ALL
SELECT 1, 5, 0 UNION ALL
SELECT 1, 6, 0 UNION ALL
SELECT 1, 7, 0 UNION ALL
SELECT 1, 8, 0 UNION ALL
SELECT 1, 1, 0 UNION ALL
SELECT 1, 2, 0 UNION ALL
SELECT 1, 3, 0 UNION ALL
SELECT 2, 9, 0 UNION ALL
SELECT 2, 10, 0 UNION ALL
SELECT 2, 11, 0 UNION ALL
SELECT 2, 12, 0 UNION ALL
SELECT 2, 13, 0 UNION ALL
SELECT 2, 14, 0 UNION ALL
SELECT 2, 15, 0

-- One Non-cursor way
UPDATE i 
SET ReprintCount =
	(
		SELECT COUNT(*)
		FROM @ReprintInfo ri
		WHERE ri.ReprintID <= i.ReprintID
		AND ri.JobID = i.JobID
	)
FROM @ReprintInfo i

SELECT * FROM @reprintinfo

-- Cursor way
Declare @RpID int 
Declare @JobID int
declare @ID int
declare @cnt int

set @cnt = 0
set @ID = 0

declare c1 cursor for

select ReprintID, JobID from @ReprintInfo order by JobID
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 ReprintID = @RpID 

set @ID = @JobID

fetch next from c1 into @RpID,@JobID

end

close c1
deallocate c1


SELECT * FROM @reprintinfo

As you can see there is a slight difference in the result, which may or maynot be acceptable.

-yan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources