存储过程

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER    PROCEDURE TbTemp
  @kid    varchar(10),
  @lid      varchar(10),
  @usergh    varchar(10),
  @userxm varchar(50),
  @userip varchar(15),
  @userzb varchar(50)
AS
  declare  @state varchar(4),
            ---@info   varchar(100),
           @ExamNum varchar(20),
           @SqlStr varchar(1000),
    @sql varchar(1000),
           @Sql_str varchar(1000),
           @sql_instr varchar(100),
           @recorduser   int,
           @E_Ids varchar(800),
    @E_Id  int,
    @E_Name varchar(800),
    @E_A varchar(500),
    @E_B varchar(500),
    @E_C varchar(500),
    @E_D varchar(500),
    @E_E varchar(500),
    @Tnumber  varchar(15),
          @Restsql varchar(200)
---创建临时表结构开始-------
create table #Tb
(E_Id int,
E_Name varchar(500),
E_A varchar(300),
E_B varchar(300),
E_C varchar(300),
E_D varchar(300),
E_E varchar(300),
Tnumber varchar(15))

---创建临时表结构结束---------
Set @state='A011'        
--set @info='可以正常考试'
set @ExamNum=''
set @E_Ids=''
 set @recorduser=(select count(*) from DT_ExamStore where (E_Sid=@kid and E_Level=@lid))
 if @recorduser<2
      begin
        -- Set @info='警告：题库量太少，无法进行考试，请输入考题！'   
          Set @state='A001'
   select @state as state --,@ExamNum as Num --,@info as info,
      end
 else
     begin
        -- Set @info='OK'     ----dbo.MakeFileName(@tempsj)
          Set @state='A011'
   --计算试卷编号日期+工号+随机数----
          set @ExamNum =(cast(year(getdate()) as varchar(4))+cast(month(getdate()) as varchar(2))+cast(day(getdate()) as varchar(2)))+Ltrim(Rtrim(@usergh))+cast(cast(rand()*1000 as int) as varchar(4))
 ---往考试结果表录入--------print(@Restsql)
          set @Restsql=' Insert into DT_ExamResult (R_UserNum,R_UserName,R_Level,R_Subject,R_PId,R_IP,R_Group,R_Score,R_ExamCount,R_ExamSub) values ('''+Ltrim(Rtrim(@usergh))+''','''+Ltrim(Rtrim(@userxm))+''','''+
                   Ltrim(Rtrim(@lid))+''','''+Ltrim(Rtrim(@kid))+''','''+ Rtrim(Ltrim(@ExamNum))+''','''+Rtrim(Ltrim(@userip))+''','''+Rtrim(Ltrim(@userzb))+''',0, 1, 0)'
          exec(@Restsql)
 ----随机抽题开始------
          set @SqlStr='select top 2 E_Id,E_Name,E_A,E_B,E_C,E_D,E_E,'+@ExamNum+' as Tnumber from  DT_ExamStore where (E_Sid='+  @kid+' and E_Level='+ @lid +') ORDER BY NEWID()'
          set @sql='declare exampaper scroll cursor for ' + @SqlStr
          exec(@sql)
 ---游标开始------
          open exampaper
           fetch next from exampaper into @E_Id,@E_Name,@E_A, @E_B,@E_C,@E_D,@E_E,@Tnumber
             while @@fetch_status=0
                 begin
        ----更新题库抽次----
                       set @Sql_str='update DT_ExamStore set E_Sum=E_Sum+1 where E_Id='+ cast (@E_Id as nvarchar(20))
                        exec(@Sql_str)
   --计算ID集将录入试卷---
                        set @E_Ids=@E_Ids+cast(@E_Id as nvarchar(10))+','
   --往临时表录入-----
                        insert into #Tb(E_Id,E_Name,E_A,E_B,E_C,E_D,E_E,Tnumber) values(@E_Id,@E_Name,@E_A, @E_B,@E_C,@E_D,@E_E,@Tnumber)
                 set @E_Id=null
   set @E_Name=null
   set @E_A=null
   set @E_B=null
   set @E_C=null
   set @E_D=null
   set @E_E=null
   set @Tnumber=null
   --录入结束----
                      fetch next from exampaper into @E_Id,@E_Name,@E_A,@E_B,@E_C,@E_D,@E_E,@Tnumber
              end
     --往试卷表录入---
            set @sql_instr='Insert into DT_Paper (P_EId,P_Number) values ( '''+cast(@E_Ids as nvarchar(800))+''','+cast(@Tnumber as nvarchar(12))+')'
            exec(@sql_instr)
     set @E_Ids=null

           close exampaper
       deallocate exampaper
 ---游标结束------
  --打开临时表并失放----
     select E_Id,E_Name,E_A,E_B,E_C,E_D,E_E,Tnumber from #Tb
     drop table  #Tb
  end
 set nocount off

 

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO