[Original] A stored procedure in a test system used to generate a test paper.

Stored Procedure | original
CREATE proc Paperbuild @papername char (@subject char), @xzt int, @xztn int, @tkt int, @tktn int, @pdt int, @pdtn int,
@wdt int, @wdtn int as
/*
Power By:liujun
Address: Computer department, Hengyang Normal University, class 0102
Selection Question Generation section
*/
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= ' multiple-choice ' 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= ' multiple-choice ')

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= ' multiple-choice ' and subject= ' + @subject + ' and difficulty < ' +str (@lsnd) + ' ' and Ken in ' + @ken + ' and Question1 to ' (select Question1 from paper where q_type= ' multiple-choice ' and paper Name= ' + @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= ' multiple-choice ' and subject= ' + @subject + ' and difficulty > ' +str (@lsnd) + ' ' and Ken in ' + @ken + ' and Question1 to ' (select Question1 from paper where q_type= ' multiple-choice ' and paper Name= ' + @papername + '] ORDER by newid () '
Execute (@tempsql)
End
Set @tempsql = ' Update paper set Papername= ' + @papername + ' where papername is null '
Execute (@tempsql)
/*
Fill in the blanks in the production section
*/
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= ' fill in the blanks ' 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= ' Fill in the blanks ')

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= ' fill in the blanks ' and subject= ' + @subject + ' and difficulty < ' +str (@lsnd) + ' ' and Ken in ' + @ken + ' and Question1 to ' (select Question1 from paper where q_type= ' fill in the blanks ' and Pape Rname= ' + @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= ' fill in the blanks ' and subject= ' + @subject + ' and difficulty > ' +str (@lsnd) + ' ' and Ken in ' + @ken + ' and Question1 to ' (select Question1 from paper where q_type= ' fill in the blanks ' and paper Name= ' + @papername + '] ORDER by newid () '
Execute (@tempsql)
End
Set @tempsql = ' Update paper set Papername= ' + @papername + ' where papername is null '
Execute (@tempsql)

/*
The generation part of the judgment question
*/
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= ' judgment question ' 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= ' Judgment question ')
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= ' judgment question ' and subject= ' + @subject + ' and difficulty < ' +str (@lsnd) + ' ' and Ken in ' + @ken + ' and Question1 to ' (select Question1 from paper where q_type= ' judgment question ' and Pape Rname= ' + @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= ' judgment question ' and subject= ' + @subject + ' and difficulty > ' +str (@lsnd) + ' ' and Ken in ' + @ken + ' and Question1 to ' (select Question1 from paper where q_type= ' judgment question ' and Pape Rname= ' + @papername + '] ORDER by newid () '
Execute (@tempsql)
End
Set @tempsql = ' Update paper set Papername= ' + @papername + ' where papername is null '
Execute (@tempsql)

/*
Ask the answer to generate part
*/
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= ' question and answer ' 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= ' questions and Answers ')
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= ' question and answer ' and subject= ' + @subject + ' and Ken in ' +@ ken+ ' and difficulty< ' +str (@lsnd) + ' and Question1 ' (select Question1 from paper where q_type= ' questions and answers ' and Papernam E= ' + @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= ' question and answer ' and subject= ' + @subject + ' and Ken in ' +@ ken+ ' and difficulty> ' +str (@lsnd) + ' and Question1 ' (select Question1 from paper where q_type= ' questions and answers ' and Papernam E= ' + @papername + '] ORDER by newid () '
Execute (@tempsql)
End
Set @tempsql = ' Update paper set Papername= ' + @papername + ' where papername is null '
Execute (@tempsql)