分类 计算 挑选的存贮过程

CREATE procedure xp_lucky_programe_1
@username nvarchar(20),
@int_big_class int
as
--收藏总的节目总数
declare @count_program  int
select @count_program=count(*)
from walkradio
where w_username=@username
if(@count_program>=1)
begin
--收藏的类别总数(按大类计算)
declare @count_big_class int
select  @count_big_class=count(distinct p_big_class)
from program_info,walkradio
where
program_info.program_id=walkradio.p_id
and w_username= @username
--计算某个类别的节目总数在总的节目数的比例
declare @int_property decimal(5,2)
declare @SQLStr nvarchar(1000) 
declare @exec_sql nvarchar(1000)
declare @exec_sql_1 nvarchar(1000)
set @exec_sql_1=''
declare @select_count_programe int --应该挑选的节目数
declare @p_big_class int
declare @i int
declare @x int
declare @postion cursor
set @postion  = cursor scroll for
--每个类别对应的节目总数和类别名称
select distinct count(*) as lucky_count,p_big_class
from program_info,walkradio
where
program_info.program_id=walkradio.p_id
and w_username=@username
group by(p_big_class)
open @postion
fetch next from @postion into @x,@p_big_class
  set @i=1
  while(@i<=@count_big_class)
begin
set @int_property=0.1*12*@x/@count_program
set @select_count_programe=ceiling(@int_property*10)
set @SQLStr='select top '+ convert(varchar(10),@select_count_programe) + ' * from good_progrm where p_big_class='+convert(varchar(10),@p_big_class)
set @exec_sql_1=@exec_sql_1+@SQLStr+' union '
--到了最后 就不再加 union这个关键字
if(@i=@count_big_class)
begin
set @exec_sql_1=@exec_sql_1+@SQLStr
end
fetch next from @postion into @x,@p_big_class
set @i=@i+1
end
close @postion
set @exec_sql='select * from( '
set @exec_sql=@exec_sql+@exec_sql_1
set @exec_sql=@exec_sql+') b where program_id not in (select walkradio.p_id from walkradio where w_username='''+@username+''') and p_big_class ='+convert(varchar(10),@int_big_class)+'order by p_see_count desc,program_id desc'
exec (@exec_sql)
end
else
begin
print '你没有收藏节目'
select  top 3 * from good_progrm  where p_big_class=@int_big_class order by p_see_count desc
end
GO