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

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)

原文转自：http://www.ltesting.net