microsoft, sql, server 2000 database, create view issue

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
rutgermonsAsked:
Who is Participating?
 
nito8300Connect With a Mentor 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
0
 
rutgermonsAuthor Commented:
thanks

but how can i use this to join to another table?
0
All Courses

From novice to tech pro — start learning today.