Rewrite cursor into while loop

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

Rewrite cursor into while loop

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2009-06-22 : 11:28:21

Hi,

I am trying to rewrite this cursorinto a while loop. Please help.

DECLARE @rec_counter int, @sale_link_id int
DECLARE @parcel_id varchar(15), @save_pid varchar(15)
DECLARE @sale_date datetime

SET @save_pid = ''

DECLARE pas_cursor CURSOR
STATIC
FOR
select parcel_id, sale_date, sale_link_id
from conv_inv_pas_map
order by parcel_id, sale_date
OPEN pas_cursor

FETCH FIRST FROM pas_cursor
INTO @parcel_id, @sale_date, @sale_link_id

WHILE @@FETCH_STATUS = 0
BEGIN
IF @parcel_id <> @save_pid
BEGIN
SET @save_pid = @parcel_id
SET @rec_counter = 0
END
SET @rec_counter = @rec_counter + 1
update conv_inv_pas_map set recid1 = @rec_counter where sale_link_id = @sale_link_id
-- print 'read pid = ' + @parcel_id + ' ' + cast(@rec_counter as varchar(10))
FETCH NEXT FROM pas_cursor
INTO @parcel_id, @sale_date, @sale_link_id
END

CLOSE pas_cursor
DEALLOCATE pas_cursor

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 11:35:46

seems like what you need is this


update c set c.recid1 = (select count(*) from conv_inv_pas_map where parcel_id = c.parcel_id and sale_date < c.sale_date)+1
from conv_inv_pas_map c

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources