Posted by Dan on 01/02/07 19:42

Hi all, I am creating a search table where the keywords field is made
up of several text fields and this is causing me some problems. I can
concatentate the text ok but i can't seem to concatenate matching
records here is the cursor loop. I'm not a fan of cursors but also
didn't see another way of achieving this.

declare @ptr1 varbinary(16)
declare @Ptr2 varbinary(16)
declare @profileid int
declare @x int

set @profileid = 0

while @profileid is not null
begin

select
@profileid = min([id]),
@ptr1 = MIN(textptr(text1))
from #holding
where [id] > @profileid


declare c2 cursor fast_forward for
select textptr(searchterms), datalength(searchterms)
from search
where search.[id] = @profileid

open c2

fetch c2 into @ptr2, @x

while @@fetch_status = 0
begin
updatetext search.searchterms @ptr2 null 0 #holding.text1 @ptr1
fetch c2 into @ptr2, @x
end

close c2
deallocate c2

end

The #holding table contains the fields that i want to concatenate and
the search table is the resulting table. This example would loop
through search and find id 1 in search and then append another field
matching id 1 in holding then move onto the next field in turn going
through the whole table.

i.e.

search holding result after each loop
id text id text
1 abc 1 def abcdef
2 ghi 2 jkl ghijkl


When I run this, some of the records concatenate properly but most dont
with the same text being appended to the end of searchterms. i.e loads
of results will end up with jkl tagged onto the end. I can't figure out
when my loop is falliing over!!! Can anyone help?

Dan

[Back to original message]