Sqlserver存储过程中经常使用的循环

1：游标的使用：

SQL Server游标的使用【转】
http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html

declare @date2 datetime
declare tabB_cursor cursor for  
select date,weekNum,studyTime  from @tabB WHERE date > @date order by date

open tabB_cursor
fetch NEXT from tabB_cursor into @date2,@weekNumB,@studyTimeB
WHILE @@FETCH_STATUS = 0
begin
    if @weekNumB !=7
     insert into @tabC values (@weekNum,@date2,@sequence,@courseID,@chapterID,@studyTime)
    else if @weekNumB=7
    begin
        set @weekNum=@weekNum+1
    end
    fetch NEXT from tabB_cursor into @date2,@weekNumB,@studyTimeB
end
close tabB_cursor
deallocate tabB_cursor

2：使用表变量：

USE [DB]
GO
/****** Object:  StoredProcedure [dbo].[P_TeacherStatis]    Script Date: 11/26/2014 09:07:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROC [dbo].[P_TeacherStatis]

AS
    DECLARE @TeacherIDSet TABLE (teacherID INT,status INT)
    DECLARE @TeaNum INT,
            @v_teacherID varchar(max),
            @v_adviserClassNum INT,
            @v_adviserMemberTotal INT,
            @v_adminClassNum  INT,
            @v_adminMemberTotal INT,
            @errorCode INT
    
    SET    @errorCode=0
    
    --开启事物
    Begin           
        Set    NOCOUNT    ON;           
        Set XACT_ABORT ON;           
    BEGIN TRY
    BEGIN TRANSACTION
    -- 直接清空表
    TRUNCATE TABLE ADVC_TEACHER_STATIS

    --创建 所有的老师的teacherID 集合
    INSERT INTO @TeacherIDSet (teacherID,status)  select  teacherID,1   from ADVC_TEACHER  WITH (NOLOCK) WHERE  status=1

    SELECT @TeaNum=COUNT(*) FROM @TeacherIDSet WHERE status=1
    WHILE @TeaNum>0
    BEGIN
      SELECT TOP 1 @v_teacherID=teacherID FROM @TeacherIDSet WHERE status=1
          
        -- 这个老师是班主任 管理的班级,管理的班级个数
        select @v_adviserClassNum = COUNT(case when act.teacherType =1 then 1 end),@v_adminClassNum = count(*)  
        from ADVC_CLASS_TEACHER  act WITH (NOLOCK)   
        inner join  ADVC_CLASS ac  WITH (NOLOCK)   on ac.classID = act.classID   
        where   ac.status=1 and  act.teacherID =  @v_teacherID  
        
        -- 作为班主任 管理学员总数
        SELECT @v_adviserMemberTotal = SUM(case when CT.teacherType = 1 then C.currCount ELSE 0 end)
        ,@v_adminMemberTotal = SUM(C.currCount)
        FROM ADVC_CLASS C WITH(NOLOCK)
        inner JOIN  ADVC_CLASS_TEACHER CT WITH (NOLOCK) ON CT.classID = C.classID  
        inner JOIN  ADVC_TEACHER TEA WITH (NOLOCK) ON CT.TeacherID = TEA.TeacherID    
        where C.status=1  and  CT.teacherID = @v_teacherID


    -- 直接清空了，下面就直接插入
       INSERT INTO [ADVC_TEACHER_STATIS]
       ([teacherID],[adviserClassNum],[adviserMemberTotal],[adminClassNum],[adminMemberTotal])
        values
       (@v_teacherID,@v_adviserClassNum,@v_adviserMemberTotal,@v_adminClassNum,@v_adminMemberTotal)
 
       UPDATE @TeacherIDSet SET status = 0 WHERE teacherID=@v_teacherID
       SELECT @TeaNum=COUNT(*) FROM @TeacherIDSet WHERE status=1
      
     END
--结束事物
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
     ROLLBACK TRANSACTION
     SET @errorCode=-1 --失败
  END CATCH
  End
 --print convert(varchar(20),@errorCode)
  RETURN @errorCode