SQL Server Performance

Row by Row without Cursor

Discussion in 'Getting Started' started by vcs161, Feb 8, 2010.

  1. vcs161 New Member

    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
  2. Flexdog New Member

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

    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?
  4. Flexdog New Member

    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
  5. Flexdog New Member

    Oh wait,
    you don't need the while @end loop either.
  6. vcs161 New Member

    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?
  7. Flexdog New Member

    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
  8. Madhivanan Moderator

Share This Page