Row by Row without Cursor | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Row by Row without Cursor

My goal here is to compile/concatenate text from multiple lines for each unique rep_id. Each 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 #rep_id is the primary key I want to map when I build the full rep_text 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 to get a full result set.
I found this reference that I believe is close to what I am trying to accomplish, but can’t seem to figure it out with mine. Any help or input is GREATLY appreciated.
http://www.sql-server-performance.com/articles/per/operations_no_cursors_p3.aspx

Try:
select @the_full_report = case when @line_count = 0 then reptxt_text else ISNULL(@the_full_report,"") + " " + reptxt_text
end,

Thank you so much for your valuable time and patience. This does work, but I still only receive one result and should receive over 130 in this set. I seem to be using the insert variable values at the wrong time and can’t seem to figure out where I am going wrong? No matter what I do, I get the same thing. Can you help me understand where and how I can populate all the records after each row for the @rep_id and @the_full_report has been built with the full text into the #rep_Text temp table?

don’t need @line_count and beside increment @line_count within select is indeterministic.
select @the_full_report = isnull(@the_full_report,"") + case when isnull(@the_full_report,"") <> "" then " " else "" end + reptxt_text
from risdb_rch05_stag..report_text t1
where reptxt_rep_id = @rep_id and reptxt_seq_no_s >= 0
order by reptxt_seq_no_s asc

Oh wait,
you don’t need the while @end loop either.

It seems that this approach will only pull the last sentence or so and yet will pull the full text as I need it with my original approach. But I still only get one result and I just do not understand why that is. How do I build all of the variable values into a table from this while statement and not just the first one? For some reason I am only mapping to one #pp_rep_id where I have the reptxt_rep_id = @rep_id. Do I need to set a max rep_id from my table table then do a while @rep_id < that max value as this compiles?

Here is almost working pseudocode – good lucks:
declare @id int, @rep_id rep_id_type , @the_full_report varchar(16384)
create table #daily_read_rep — <=== Add an ‘id’ identity(int,1,1) column, use that to loop thru this temp table
/*
a bunch of code is here to build this table and the #rep_id is the primary key I want to map when I build the full rep_text
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
set @id = 0
while 1 > 0
begin
select @rep_id = isnull(rep_id,0),
@id = isnull(id,0)
from #daily_read_rep
where id = @id + 1
if (@id <1)
break SELECT @the_full_report = "" select @the_full_report = isnull(@the_full_report,"") + case when isnull(@the_full_report,"") <> "" then " " else "" end + reptxt_text
from risdb_rch05_stag..report_text
where reptxt_rep_id = @rep_id and reptxt_seq_no_s >= 0
order by reptxt_seq_no_s asc
insert into #rep_Text values (@rep_id, @the_full_report) end — while

Seems you need something like this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |