[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

microsoft, sql server, 2000, cursor query

Posted on 2008-01-25
5
Medium Priority
?
539 Views
Last Modified: 2008-01-25
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
0
Comment
Question by:rutgermons
  • 4
5 Comments
 
LVL 2

Accepted Solution

by:
doswel1 earned 2000 total points
ID: 20745946
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

0
 
LVL 2

Expert Comment

by:doswel1
ID: 20745957
oops Line 21 should be;
insert #temptable
0
 
LVL 2

Expert Comment

by:doswel1
ID: 20745974
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.
0
 

Author Comment

by:rutgermons
ID: 20746096
doswell

could you modify the query to suit?
0
 
LVL 2

Expert Comment

by:doswel1
ID: 20746309
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

0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question