cursor to select data from each row is in infinite loop -
this site has helped me in past. decided try asking own question:
i have trim , concatenate strings stored in separate records. column in 1 table holds 255 characters. overflow text stored in separate table , column holds 255 characters. so, table may have have multiple records same text. bad software have use.
when refer getting rid of junk in column in comments, mean crap throw in @ end of each string indicate there more text in record.
my problem cursors seems in infinite loop; string returned continuously repeats entire text.
any appreciated.
alter function [dbo].[returndesc] (@id int) returns varchar(7000) begin /*getting rid of junk in event_text (if there any) , setting @entiredesc event_text*/ declare @entiredesc varchar(7000) declare @cnt int declare @data varchar(5000) select @cnt = count(*) events e, oevent_text o e.event_id = o.event_id , e.event_id = @id --data in oevent_text, need strip if @cnt > 0 select @entiredesc = left(t.event_text, len(t.event_text) - 4) events e, event_text t e.event_id = t.event_id , e.event_id = @id --there no data in oevent_text else select @entiredesc = t.event_text events e, event_text t e.event_id = t.event_id , e.event_id = @id /*getting rid of junk in oevent_text, concatenating @entiredesc, determine last record doesn't stripped*/ declare @rows int declare cur cursor local scroll select data events e, oevent_text o e.event_id = o.event_id , e.event_id = @id order o.segm open cur fetch next cur @data --returns number of rows in cursor set @rows = @@cursor_rows while @@fetch_status = 0 begin --if last record, don't strip characters if @rows = 1 select @entiredesc = @entiredesc + o.data events e, oevent_text o e.event_id = o.event_id , e.event_id = @id --4 characters need stripped @ end else select @entiredesc = @entiredesc + left(o.data, len(o.data) - 4) r25.dbo.events e, oevent_text o e.event_id = o.event_id , e.event_id = @id set @rows = @rows - 1 fetch next cur @data end close cur deallocate cur return @entiredesc end
try adding begin , end if statements
--if last record, don't strip characters if @rows = 1 begin select @entiredesc = @entiredesc + o.data events e, oevent_text o e.event_id = o.event_id , e.event_id = @id --4 characters need stripped @ end end else begin select @entiredesc = @entiredesc + left(o.data, len(o.data) - 4) r25.dbo.events e, oevent_text o e.event_id = o.event_id , e.event_id = @id end
Comments
Post a Comment