sql server 游标

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 徐本县
-- Create date: <Create Date,,>
-- Description: 删除班级
-- =============================================
ALTER PROCEDURE DeleteClass
-- Add the parameters for the stored procedure here
@classId int,
@Msg nvarchar(300)='' OUTPUT -- 返回提示信息
AS
BEGIN
DECLARE @ROWNUM int
select @ROWNUM=count(*) from zq_baby where classId=@classId and isDel=0
IF @ROWNUM>0
BEGIN
set @Msg='该班级下还有幼儿,请先删除班级内幼儿'
END
ELSE
BEGIN
begin tran
begin try
DECLARE @teacherUid int
BEGIN
DECLARE c_teacher_list CURSOR FAST_FORWARD FOR --创建一个 c_teacher_list 游标
select teacherUid from zq_class_teacher where classId=@classId and isDel=0
OPEN c_teacher_list; --打开游标
FETCH NEXT FROM c_teacher_list INTO @teacherUid;
WHILE @@fetch_status = 0
BEGIN
print @teacherUid
DECLARE @NUM int

select @NUM=count(*) from zq_class_teacher where teacherUid=@teacherUid -- 判断该老师是否有多个班级
update zq_class_teacher set isDel=1 where teacherUid=@teacherUid and classId=@classId -- 移除绑定关系
IF @@ROWCOUNT>0
BEGIN
DECLARE @isDel Bit
IF @NUM=1
BEGIN
set @isDel='true'
DECLARE @th_Id int
DECLARE @hardwareId int
select @th_Id=Id,@hardwareId=hardwareId from zq_teacher_hardware where teacherUid=@teacherUid
IF @th_Id>0
BEGIN
DECLARE @h_id int
select @h_id=id from zq_hardware where id=@hardwareId
IF @h_id>0
BEGIN
update zq_hardware set status=2 where id=@h_id
delete zq_teacher_hardware where teacherUid=@teacherUid
END
END
delete marke_shift_user where Uid=@teacherUid
END
update zq_user set lastModifyTime=getdate() where Uid=@teacherUid
FETCH NEXT FROM c_teacher_list INTO @teacherUid;
END
END
END
delete from zq_class where id=@classId
end try
begin catch
select Error_message() as ErrorMessage --错误的具体信息
if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
rollback tran ---由于出错，这里回滚到开始。
set @Msg=Error_message()
end catch
if(@@trancount>0)
commit tran --如果成功提交所有操作
set @Msg='success'
END
END
GO