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,@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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!