为什么这个存储过程会错呢????拜求!!!
-------------------------------------------------单科成绩细化表
CREATE proc dkcjxh(@subjectid int)
as
select rrt_examclass,rrt_studentid,rrt_mark into # from student where rrt_examsubjectid=@subjectid order by rrt_examclass,rrt_studentid
--select * from #
--drop table #
declare @sql0 nvarchar(4000),@sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000),@sql5 nvarchar(4000),@sql6 nvarchar(4000),@sql7 nvarchar(4000),@sql8 nvarchar(4000),@sql9 nvarchar(4000),@sql10 nvarchar(4000)
set @sql0=''
set @sql1=''
set @sql2=''
set @sql3=''
set @sql4=''
set @sql5=''
set @sql6=''
set @sql7=''
set @sql8=''
set @sql9=''
set @sql10=''
select @sql0=@sql0+',cast(sum(case when rrt_examclass='+cast(rrt_examclass as varchar(10))+' then 1 else 0 end) as nvarchar)+''/''+cast((select count(*) from # where rrt_examclass='+cast(rrt_examclass as varchar(10))+' and rrt_mark>=140) as nvarchar) as ['+cast(rrt_examclass as varchar(10))+'班]'
,@sql1=@sql1+',cast(sum(case when rrt_examclass='+cast(rrt_examclass as varchar(10))+' then 1 else 0 end) as nvarchar)+''/''+cast((select count(*) from # where rrt_examclass='+cast(rrt_examclass as varchar(10))+' and rrt_mark>=130) as nvarchar) as ['+cast(rrt_examclass as varchar(10))+'班]'
,@sql2=@sql2+',cast(sum(case when rrt_examclass='+cast(rrt_examclass as varchar(10))+' then 1 else 0 end) as nvarchar)+''/''+cast((select count(*) from # where rrt_examclass='+cast(rrt_examclass as varchar(10))+' and rrt_mark>=120) as nvarchar) as ['+cast(rrt_examclass as varchar(10))+'班]'
,@sql3=@sql3+',cast(sum(case when rrt_examclass='+cast(rrt_examclass as varchar(10))+' then 1 else 0 end) as nvarchar)+''/''+cast((select count(*) from # where rrt_examclass='+cast(rrt_examclass as varchar(10))+' and rrt_mark>=110) as nvarchar) as ['+cast(rrt_examclass as varchar(10))+'班]'
,@sql4=@sql4+',cast(sum(case when rrt_examclass='+cast(rrt_examclass as varchar(10))+' then 1 else 0 end) as nvarchar)+''/''+cast((select count(*) from # where rrt_examclass='+cast(rrt_examclass as varchar(10))+' and rrt_mark>=100) as nvarchar) as ['+cast(rrt_examclass as varchar(10))+'班]'
,@sql5=@sql5+',cast(sum(case when rrt_examclass='+cast(rrt_examclass as varchar(10))+' then 1 else 0 end) as nvarchar)+''/''+cast((select count(*) from # where rrt_examclass='+cast(rrt_examclass as varchar(10))+' and rrt_mark>=90) as nvarchar) as ['+cast(rrt_examclass as varchar(10))+'班]'
,@sql6=@sql6+',cast(sum(case when rrt_examclass='+cast(rrt_examclass as varchar(10))+' then 1 else 0 end) as nvarchar)+''/''+cast((select count(*) from # where rrt_examclass='+cast(rrt_examclass as varchar(10))+' and rrt_mark>=80) as nvarchar) as ['+cast(rrt_examclass as varchar(10))+'班]'
,@sql7=@sql7+',cast(sum(case when rrt_examclass='+cast(rrt_examclass as varchar(10))+' then 1 else 0 end) as nvarchar)+''/''+cast((select count(*) from # where rrt_examclass='+cast(rrt_examclass as varchar(10))+' and rrt_mark>=70) as nvarchar) as ['+cast(rrt_examclass as varchar(10))+'班]'
,@sql8=@sql8+',cast(sum(case when rrt_examclass='+cast(rrt_examclass as varchar(10))+' then 1 else 0 end) as nvarchar)+''/''+cast((select count(*) from # where rrt_examclass='+cast(rrt_examclass as varchar(10))+' and rrt_mark>=60) as nvarchar) as ['+cast(rrt_examclass as varchar(10))+'班]'
,@sql9=@sql9+',cast(sum(case when rrt_examclass='+cast(rrt_examclass as varchar(10))+' then 1 else 0 end) as nvarchar)+''/''+cast((select count(*) from # where rrt_examclass='+cast(rrt_examclass as varchar(10))+' and rrt_mark>=50) as nvarchar) as ['+cast(rrt_examclass as varchar(10))+'班]'
,@sql10=@sql10+',cast(sum(case when rrt_examclass='+cast(rrt_examclass as varchar(10))+' then 1 else 0 end) as nvarchar)+''/''+cast((select count(*) from # where rrt_examclass='+cast(rrt_examclass as varchar(10))+' and rrt_mark>=0) as nvarchar) as ['+cast(rrt_examclass as varchar(10))+'班]' from # group by rrt_examclass order by rrt_examclass
exec ('
select ''140-150'' as 分数段'+@sql0+',cast(count(*) as nvarchar)+''/''+cast((select count(*) from # where rrt_mark>=140) as nvarchar) as [年段]
from #
where rrt_mark>=140
union all
select ''130-139'' as 分数段'+@sql1+',cast(count(*) as nvarchar)+''/''+cast((select count(*) from # where rrt_mark>=130) as nvarchar) as [年段]
from #
where rrt_mark<140 and rrt_mark>=130
union all
select ''120-129'' as 分数段'+@sql2+',cast(count(*) as nvarchar)+''/''+cast((select count(*) from # where rrt_mark>=120) as nvarchar) as [年段]
from #
where rrt_mark<130 and rrt_mark>=120
union all
select ''110-119'' as 分数段'+@sql3+',cast(count(*) as nvarchar)+''/''+cast((select count(*) from # where rrt_mark>=110) as nvarchar) as [年段]
from #
where rrt_mark<120 and rrt_mark>=110
union all
select ''100-109'' as 分数段'+@sql4+',cast(count(*) as nvarchar)+''/''+cast((select count(*) from # where rrt_mark>=100) as nvarchar) as [年段]
from #
where rrt_mark<110 and rrt_mark>=100
union all
select ''90-99'' as 分数段'+@sql5+',cast(count(*) as nvarchar)+''/''+cast((select count(*) from # where rrt_mark>=90) as nvarchar) as [年段]
from #
where rrt_mark<100 and rrt_mark>=90
union all
select ''80-89'' as 分数段'+@sql6+',cast(count(*) as nvarchar)+''/''+cast((select count(*) from # where rrt_mark>=80) as nvarchar) as [年段]
from #
where rrt_mark<90 and rrt_mark>=80
union all
select ''70-79'' as 分数段'+@sql7+',cast(count(*) as nvarchar)+''/''+cast((select count(*) from # where rrt_mark>=70) as nvarchar) as [年段]
from #
where rrt_mark<80 and rrt_mark>=70
union all
select ''60-69'' as 分数段'+@sql8+',cast(count(*) as nvarchar)+''/''+cast((select count(*) from # where rrt_mark>=60) as nvarchar) as [年段]
from #
where rrt_mark<70 and rrt_mark>=60
union all
select ''50-59'' as 分数段'+@sql9+',cast(count(*) as nvarchar)+''/''+cast((select count(*) from # where rrt_mark>=50) as nvarchar) as [年段]
from #
where rrt_mark<60 and rrt_mark>=50
union all
select ''0-49'' as 分数段'+@sql10+',cast(count(*) as nvarchar)+''/''+cast((select count(*) from # where rrt_mark>=0) as nvarchar) as [年段]
from #
where rrt_mark<50
')
drop table #
GO
请各位好心人有耐心的帮我看看好吗?在这里拜求!!!!