[原創]一個考試系統中的存儲過程用來生成試卷用的

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.kjueaiud.com