34,838
社区成员




SELECT TOP 10 ID=IDENTITY(INT,1,1),1 AS Num,name INTO CurTab FROM sys.tables
ALTER TABLE CurTab ADD CONSTRAINT CHK_CurTab_Num CHECK(Num/10%2=0)
GO
DECLARE @ErrorRows INT=0;
DECLARE Cur1 CURSOR LOCAL FOR
SELECT ID FROM CurTab
DECLARE @ID INT
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
BEGIN TRY
UPDATE CurTab SET Num=Num+ID*10 WHERE CURRENT OF Cur1;
END TRY
BEGIN CATCH
SET @ErrorRows=@ErrorRows+1
END CATCH
FETCH NEXT FROM Cur1 INTO @ID
END
CLOSE Cur1
DEALLOCATE Cur1
SELECT @ErrorRows AS 出错记录数
/*
出错记录数
5
*/
GO
SELECT ID,Num FROM CurTab
/*
ID Num
1 1
2 21
3 1
4 41
5 1
6 61
7 1
8 81
9 1
10 101
*/
DROP TABLE CurTab
fetch from ss into @a
while @@FETCH_STATUS = 0
begin
-- 把 try catch 写到 循环中
begin try
update test set .... / -- 成功的处理
end try
begin catch
update .... -- 失败的处理
end catch
-- 下一个
fetch from ss into @a
end