[原创]一个考试系统中的存储过程用来生成试卷用的

CREATE proc paperbuild @papername char(200), @subject char(10), @xzt int,@xztn int,@tkt int,@tktn int,@pdt int,@pdtn int,
@wdt int,@wdtn int as
/*
power by ：liujun
address:衡阳师范学院计算机系0102班
选择题生成部分
*/
declare @bxzt int,@ken varchar(8000)
declare @tempsql varchar(8000),@lxzt int,@lsnd int
set @bxzt=@xzt/2
set @ken=(select top 1 kenlist from tempkenlist where papername=@papername)
set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@bxzt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''选择题'' and subject='''+@subject+''' and ken in '+@ken+' order by newid()'
execute(@tempsql)

set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)

set @lxzt=@xzt-@bxzt
set @lsnd=(select avg(difficulty) from paper where papername=@papername and q_type='选择题')

if @lsnd>@xztn
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@lxzt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''选择题'' and subject='''+@subject+''' and difficulty<'''+str(@lsnd)+''' and ken in '+@ken+' and question1 not in(select question1 from paper where q_type=''选择题'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
else
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@lxzt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''选择题'' and subject='''+@subject+''' and difficulty>'''+str(@lsnd)+''' and ken in '+@ken+' and question1 not in(select question1 from paper where q_type=''选择题'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)
/*
填空题生成部分
*/
declare @btkt int,@ltkt int
set @btkt=@tkt/2

set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@btkt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''填空题'' and subject='''+@subject+''' and ken in '+@ken+' order by newid()'
execute(@tempsql)

set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)

set @ltkt=@tkt-@btkt
set @lsnd=(select avg(difficulty) from paper where papername=@papername and q_type='填空题')

if @lsnd>@tktn
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@ltkt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''填空题'' and subject='''+@subject+''' and difficulty<'''+str(@lsnd)+''' and ken in '+@ken+' and question1 not in(select question1 from paper where q_type=''填空题''  and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
else
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@ltkt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''填空题'' and subject='''+@subject+''' and difficulty>'''+str(@lsnd)+''' and ken in '+@ken+' and question1 not in(select question1 from paper where q_type=''填空题'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)

/*
判断题生成部分
*/
declare @bpdt int,@lpdt int
set @bpdt=@pdt/2

set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@bpdt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''判断题'' and subject='''+@subject+''' and ken in '+@ken+' order by newid()'
execute(@tempsql)

set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)

set @lpdt=@pdt-@bpdt
set @lsnd=(select avg(difficulty) from paper where papername=@papername and q_type='判断题')
if @lsnd>@pdtn
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@lpdt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''判断题'' and subject='''+@subject+''' and difficulty<'''+str(@lsnd)+''' and ken in '+@ken+' and question1 not in (select question1 from paper where q_type=''判断题'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
else
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@lpdt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''判断题'' and subject='''+@subject+''' and difficulty>'''+str(@lsnd)+''' and ken in '+@ken+' and question1 not in (select question1 from paper where q_type=''判断题'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)

/*
问答题生成部分
*/
declare @bwdt int,@lwdt int
set @bwdt=@wdt/2

set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@bwdt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''问答题'' and subject='''+@subject+''' and ken in '+@ken+'  order by newid()'
execute(@tempsql)

set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)

set @lwdt=@wdt-@bwdt
set @lsnd=(select avg(difficulty) from paper where papername=@papername and q_type='问答题')
select @wdtn
if @lsnd>@wdtn
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@lwdt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''问答题'' and subject='''+@subject+''' and ken in '+@ken+' and difficulty<'+str(@lsnd)+' and question1 not in (select question1 from paper where q_type=''问答题'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
else
begin
 set @tempsql='insert into paper(question1,q_type,right_answer,option1,difficulty) select top '+str(@lwdt)+' question1,q_type,right_answer,option1,difficulty from question where q_type=''问答题'' and subject='''+@subject+''' and ken in '+@ken+' and difficulty>'+str(@lsnd)+' and question1 not in (select question1 from paper where q_type=''问答题'' and papername='''+@papername+''') order by newid()'
 execute (@tempsql)
end
set @tempsql='update paper set papername='''+@papername+''' where papername is null'
execute(@tempsql)