Looping through a data set

[Delphin]

What I am atttemting to do is to get all of the alerts into a single field. I can display the results set, however was thinking of using a loop function to combine all of the results into 1 field.

Here is what I have so far:

select top 1 @id = AlertCode from AccountAlert
			Where AccountKey = 6706
			And StopDate Is Null
			Order By AlertCode
set @Accountalert = @ID

I tried to place this into a loop, but now the query takes over 1/2 hour.

While @id is not null

begin
set @accountAlert = @accountalert + ', ' + @ID
select top 1 @id = Alertcode from Accountalert 
	where Alertcode > @id and AccountKey = 6706
			And StopDate Is Null
			
			Order By AlertCode
end

Select @accountalert

How would I fix this to display all of the alerts for this account into a single field?

 

[mrdenny]

Anything involving a loop will be slow.

You can try loading all the data that needs to be worked with in to a table variable, then fetching through the table variable with a cursor. It may speed things up.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[vongrunt]

How about simple:

select @accountAlert  = @accountAlert  + ', ' + @ID
from Accountalert 
where AccountKey = 6706 And StopDate Is Null
order By AlertCode

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[vongrunt]

Damn... replace "@ID" with "Alertcode" and try again

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[Delphin]

I cannot get the cursor to work. I may be doing it wrong, but I sent the 2 records into the temp taple and the loop is still taking over 30 min. All I want to to is combine the 1 field in up to 20 records into a single field.

Is there any way of doing this that will not take hours?

 

[mrdenny]

I was thinking of something more like this.

declare @tt table (AlertCode varchar(10))
insert into @tt
(AlertCode)
select AlertCode
from Accountalert 
where Alertcode > @id and AccountKey = 6706
    And StopDate Is Null
Order By AlertCode

declare @AccountAlert varchar(1000)
declare @ac varchar(10)
declare cur CURSOR for select AlertCode from @tt
open cur
fetch next from cur into @ac
while @@FETCH_STATUS = 0
BEGIN
     set @accountAlert = @AccountAlert + ', ' + @ac
     fetch next from cur into @ac
END
close cur
deallocate cur
select @accountAlert

Your problem may be all the selecting on that table that you are selecting against. This way you only select against the table once. Load all the data into memory, then run through the list in memory.

Let me know how this works for you.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[sunila7]

Have you tried vongrunt's suggestion. complete code should look like this.

Declare @accountAlert varchar(8000)
declare @id int
set @accountAlert = ''

select @accountAlert = AlertCode+ ',' +   @accountAlert  
from Accountalert 
where Alertcode > @id and AccountKey = 6706
    And StopDate Is Null
Order By AlertCode desc

select left(@accountAlert,len(@accountAlert) -1)

Sunil