We help IT Professionals succeed at work.

microsoft, sql, server 2000 database, create view issue

rutgermons
rutgermons asked
on
folks

how can I create a view out of the following script?

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
if @@fetch_status = 0  
begin
--Setup temp table
      drop table #temptable
      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
 
r
Comment
Watch Question

Commented:
exectute this:

CREATE PROCEDURE dbo.sp_myqry

as
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
if @@fetch_status = 0  
begin
--Setup temp table
      drop table #temptable
      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
-----------------------------------------------------------------------------------------------

then from the query use
exec dbo.sp_myqry

Author

Commented:
thanks

but how can i use this to join to another table?

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