利用游标循环进行更新插入的SQL事务语句

• Posted by Admin on March 11, 2010
• 0 comments

--开始事务
BEGIN TRAN

--不显示计数信息
SET NOCOUNT ON

DECLARE @UserID varchar(512)
DECLARE @a int
set @a=10000

--声明游标
DECLARE CRMPSContact_cursor CURSOR FOR
SELECT [UserID] FROM [gmcc].[dbo].[cvv_Users]

--打开游标
OPEN CRMPSContact_cursor

--取第一行的值给变量
FETCH NEXT FROM CRMPSContact_cursor
INTO @UserID

--执行语句
UPDATE [gmcc].[dbo].[cvv_Users]
SET [WorkID]=@a
WHERE [UserID]=@UserID
set @a=@a+1

--执行错误回滚
if @@error!=0
begin
rollback tran
return
end

--移动游标,其它所有行更新操作(当到结尾时退出)
WHILE @@FETCH_STATUS = 0
BEGIN
--游标移到下一行
FETCH NEXT FROM CRMPSContact_cursor
INTO @UserID

--执行语句，从第二行开始
UPDATE [gmcc].[dbo].[cvv_Users]
SET [WorkID]=@a
WHERE [UserID]=@UserID

set @a=@a+1

--执行错误回滚
if @@error!=0
begin
rollback tran
return
end

END

--关闭游标
CLOSE CRMPSContact_cursor

--释放游标
DEALLOCATE CRMPSContact_cursor

--提交所有变更
COMMIT TRAN

--恢复设置
SET NOCOUNT OFF
GO

http://www.cnblogs.com/wgx1323/archive/2007/06/12/780066.html