Looping without a cursor

Question

Looping without a cursor

Lee Rivers

SSC Rookie

Points: 33
More actions
June 24, 2004 at 8:52 am

#108529

I can't locate a good simple example of looping through data without the use of a cursor. I've had one in the past but can't locate my script. Does anyone know of any good locations for an example script?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

AKM SSCommitted Points: 1538 More actions · Answer 1

Here's one example. Suppose you have Table1 with an id field ID and a flag field AwaitingProcessing. You need to process each row that has AwaitingProcessing equal to 1; for whatever reason, this processing has to be done row-at-a-time, rather than with a set-based operation. Code:

declare @id_to_process int

select @id_to_process = min(ID) from Table1 where AwaitingProcessing = 1

while not @id_to_process is null

begin

-- do processing of the row with ID = @id_to_process

-- ...

update Table1 set AwaitingProcessing=0 where ID = @id_to_process

select @id_to_process = min(ID) from Table1 where AwaitingProcessing = 1

end

freewarefw Right there with Babe Points: 715 More actions · Answer 2

AKM,

what you could do is :

DECLARE @l_int_cpt INTEGER, @l_int_tot INTEGER,

CREATE TABLE table1 (tid NUMERIC IDENTITY, .....)

SET @l_int_cpt = 1

select @l_int_tot = count(*) from table1

(obtain max records from the table table1.)

WHILE @l_int_cpt <= @l_int_tot

BEGIN

SELECT ..... FROM table1 WHERE tid = @l_int_cpt

....

...

SET @l_int_cpt = @l_int_cpt + 1

END

I hope this is quite clear..

freewarefw Right there with Babe Points: 715 More actions · Answer 3

freewarefw

Right there with Babe

Points: 715

June 24, 2004 at 11:56 pm

#511903

Sorry, the answer is not for AKM but for Lee Rivers

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 4

Oh this is a fun one, cause cursors are soooo slow.

Here's s snippet of code from a routine i use that loops through the databases. Pretty simple, and doesn't require changing data anywhere. You just need a unique column (like dbID)

DECLARE @dbID int
SELECT  @dbID = MIN(dbid) FROM master..sysdatabases
WHILE  @dbID is not null
BEGIN
    SELECT @dbID = MIN(dbid) FROM master..sysdatabases WHERE dbid > @dbID 
    PRINT @dbID

END

Julian Kuiters
juliankuiters.id.au

steven powell Mr or Mrs. 500 Points: 557 More actions · Answer 5

here's one i use... (just a shade faster than Julian's method )

declare @clientId int

select ClientId into #ClientList from ...

while 1=1

begin

select @clientId = min(ClientId) from #ClientList where ClientId > isnull(@clientId, -1)

if @clientId is NULL break

-- some code

end

however, using a cursor is sometimes more efficient than this method because it doesn't have to scan or seek the next ClientId (this method does (obviously it's not an issue if your list of ids is small))

hope that helps.

ANTÔNIO FRIERE COSTA FILHO SSC Rookie Points: 33 More actions · Answer 6

Hi,

You can find at http://www.devx.com in an article named "Using Metadata to Solve Complex Problems".

Maybe this article can help you, however if your problem is using dynamic declared cursors (using variables to determine the cursor declaration) you can have a look at an article named "Anticipating Primary Key Violations" into http://www.sqlteam.com/item.asp?itemid=15149. This is the best one I found.

Have a good luck.