Row by Row Without Cursor - SQL Server Forums

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

Row by Row Without Cursor

Author

Topic

vcs1161
Starting Member

10 Posts

Posted - 2010-02-08 : 13:36:10

My goal here is to compile/concatenate text from multiple lines for each unique #pp_rep_id. Each #pp_rep_id can range from 1 to over 20 lines with the max of 255 characters in each one. My first temp table has the results of other data, now I want to include the full report text for each record.

declare @rep_id rep_id_type
declare @the_full_report varchar(16384),
@one_line varchar(255),
@line_count int,
@end tinyint

select @end= 0, @the_full_report = "", @line_count = 0

create table #daily_read_rep

a bunch of code is here to build this table
and the #pp_rep_id is the primary key I want to map when I build the full rep_text

create table #rep_Text --trying to insert variables values here(see below)
(
#rep_id rep_id_type ,
#rep_full_text varchar(16384)
)

This is what I have been trying to do to build each rep_id and the full text for each one. I am trying to understand how and when to insert the variable results into another temp table so I can map that to my main temp table.

Here is my code after the #daily_read_rep temp table has been built:
select @rep_id = #pp_rep_id from #daily_read_rep

while @end = 0

begin select @line_count

set rowcount 1

select @the_full_report = case when @line_count = 0 then reptxt_text else @the_full_report + " " + reptxt_text
end,

@line_count = @line_count + 1

from risdb_rch05_stag..report_text t1
where
reptxt_rep_id = @rep_id
and reptxt_seq_no_s >= @line_count

order by reptxt_seq_no_s asc

if @@rowcount = 0
select @end = 1
end

set rowcount 0

insert into #rep_Text values (@rep_id, @the_full_report)

I only get one rep_id when I run the #rep_text temp table. Why don't I get the all the other ones? I must be doing something out of sequence here because the one record I get looks good and just what I need. So I need to map the #rep_text to my #daily_read_rep table where #pp_rep_id = #rep_id to get a full result set.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 13:43:48

see

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

vcs1161
Starting Member

10 Posts

Posted - 2010-02-08 : 16:20:02

Can you help me understand the difference between STUFF and COALESCE? I am still at a loss on how to assign my @rep_id variable. And when I test the coalesce function I still get multiple lines for the same rep_id.

vcs1161
Starting Member

10 Posts

Posted - 2010-02-08 : 16:41:10

The more I think about it, couldn't I just run a "Update" on a Rep_text field based on the #pp_rep_id I already have from my temp table. Is this where I can use the STUFF or Coalesce function during this update with a case statement to keep the text in ascending order by each line number for each #pp_rep_id?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources