分考场、分座位案例

USE [testDB]
GO
/****** Object:  StoredProcedure [dbo].[pro_OLDCJ]    Script Date: 05/07/2013 09:17:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[pro_OLDCJ]
as
begin
   set nocount on ;
   if(object_id('#OLDCJ') is not null) begin drop table #OLDCJ end;
   select row_number() over(order by id asc) RowID,id,kch,zwh
   into #OLDCJ
   from OLDCJ order by RowID asc
  
   declare @page int
   select @page =ceiling((count(0)+0.0)/30) from #OLDCJ
  
   --第一步分考场
   declare @num int,@ID int,@zwh int;
   set @num = 1;
   while(@num <=@page)
   begin
      
       select RowID,id,kch into #KCH from #OLDCJ
    where RowID between (@num-1)*30+1 and @num*30
   
       update OLDCJ set kch  = @num
       from OLDCJ t1  inner join #KCH t2
    on t1.id = t2.id
   
       --第二步分座位号
       set @zwh =1;
       declare zwh_cur cursor for select id from #KCH
       open zwh_cur
       fetch next from zwh_cur into @ID
       while(@@fetch_status = 0)
       begin
       
        update OLDCJ set zwh = @zwh where id = @ID;
        set @zwh +=1;
        fetch next from zwh_cur into @ID
       end
       close zwh_cur;
       deallocate zwh_cur;
      
   
    set @num +=1;  --累加
    drop table #KCH; --没重新做一次分考场删除掉上一次分考试号
   end
  

drop table #OLDCJ;
end;

go
---调用执行
use testDB
go
exec pro_OLDCJ