We help IT Professionals succeed at work.

microsoft, sql server, 2000, cursor query

rutgermons
rutgermons asked
on
folks

how do i get the following query values from a cursor method to copy into one query out put
and not separate blocks of data

here is the code


declare @max_seq int
declare @pm       varchar(10)

declare all_pm cursor for
select pmnum from pm

open all_pm
fetch next from all_pm into @pm
while @@fetch_status = 0
begin  
       
      select @max_seq = max(interval)* 100 from pmsequence where pmnum=@pm
      select l.p, l.r
      , ( select top 1 t.jpnum from pmsequence  T
           WHERE l.r % t.interval = 0 and pmnum=@pm
             order by t.interval desc
         )
       from dbo.GetRangeValues(@pm, 1, @max_seq) l
      
      fetch next from all_pm into @pm
end
close all_pm
deallocate all_pm



all help will do

r
Comment
Watch Question

Commented:
create a temp table to store, then select everything from the temp table at the end;
open all_pm
fetch next from all_pm into @pm
if @@fetch_status = 0  --Just to ensure something is returned
begin
--Setup temp table
      select @max_seq = max(interval)* 100 from pmsequence where pmnum=@pm
      select l.p, l.r
      , ( select top 1 t.jpnum from pmsequence  T
           WHERE l.r % t.interval = 0 and pmnum=@pm
             order by t.interval desc
         ) as jpnum
into #temptable
       from dbo.GetRangeValues(@pm, 1, @max_seq) l
 
fetch next from all_pm into @pm
 
while @@fetch_status = 0
begin  
       
      select @max_seq = max(interval)* 100 from pmsequence where pmnum=@pm
insert @temptable
      select l.p, l.r
      , ( select top 1 t.jpnum from pmsequence  T
           WHERE l.r % t.interval = 0 and pmnum=@pm
             order by t.interval desc
         ) as jpnum
       from dbo.GetRangeValues(@pm, 1, @max_seq) l
      
      fetch next from all_pm into @pm
end
select * from #temptable
end
else
select null as p, null as r, null as jpnum where 1=2  --return nothing
 
close all_pm
deallocate all_pm

Open in new window

Commented:
oops Line 21 should be;
insert #temptable

Commented:
You can also use a declared temp table (likely better);

declare @temptable table (
 p as  int,
 r  as  int,
 jpnum as int
)

then just add the insert @temptable  in your loop for the select query,
and the select * from @temptable at the very end.

Author

Commented:
doswell

could you modify the query to suit?

Commented:
updated;
declare @max_seq int
declare @pm       varchar(10)
 
declare all_pm cursor for
select pmnum from pm
 
declare @temptable table (
 p  int,
 r   int,
 jpnum int
) --Im guessing on what field types p, r and jpnum are, if they are not ints then that will need to be updated
 
open all_pm
fetch next from all_pm into @pm
while @@fetch_status = 0
begin  
       
      select @max_seq = max(interval)* 100 from pmsequence where pmnum=@pm
insert @temptable
      select l.p, l.r
      , ( select top 1 t.jpnum from pmsequence  T
           WHERE l.r % t.interval = 0 and pmnum=@pm
             order by t.interval desc
         )
       from dbo.GetRangeValues(@pm, 1, @max_seq) l
      
      fetch next from all_pm into @pm
end
close all_pm
deallocate all_pm
select * from @temptable

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.