Cursor
@@FETCH_STATUS (Transact-SQL)
返回针对连接当前打开的任何游标发出的上一条游标 FETCH 语句的状态。
返回值 | 说明 |
---|---|
0 |
FETCH 语句成功。 |
-1 |
FETCH 语句失败或行不在结果集中。 |
-2 |
提取的行不存在。 |
由于 @@FETCH_STATUS 对于在一个连接上的所有游标都是全局性的,所以要谨慎使用 @@FETCH_STATUS。在执行一条 FETCH 语句后,必须在对另一游标执行另一 FETCH 语句前测试 @@FETCH_STATUS。在此连接上出现任何提取操作之前,@@FETCH_STATUS 的值没有定义。
例如,用户从一个游标执行一条 FETCH 语句,然后调用一个存储过程,此存储过程打开并处理另一个游标的结果。从被调用的存储过程返回控制后,@@FETCH_STATUS 反映的是在存储过程中执行的最后的 FETCH 语句的结果,而不是在存储过程被调用之前的 FETCH 语句的结果。
若要检索特定游标的最后提取状态,请查询 sys.dm_exec_cursors 动态管理函数的 fetch_status 列。
以下示例用 @@FETCH_STATUS 控制一个 WHILE 循环中的游标活动。
DECLARE Employee_Cursor CURSOR FOR SELECT EmployeeID, Title FROM AdventureWorks.HumanResources.Employee; OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor; WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM Employee_Cursor; END; CLOSE Employee_Cursor; DEALLOCATE Employee_Cursor; GO
CREATE PROC usp_ClearBarcode
AS
SET NOCOUNT ON
DECLARE @charMO varchar(16),@intClearID int,@intClearCount int
SET @charMO=''
SET @intClearID=0
SET @intClearCount=0
DECLARE csClearBarcode INSENSITIVE CURSOR
FOR
SELECT ClearID,MO_Code FROM dbo.Clear_Barcode WHERE Finished=0 ORDER BY ClearID
OPEN csClearBarcode
FETCH NEXT FROM csClearBarcode
INTO
@intClearID,@charMO
WHILE @@FETCH_STATUS = 0
BEGIN
SET @intClearCount=@intClearCount+1
BEGIN TRAN ClearBarcode
---------------------------
---Backup Barcode
--------------------------
INSERT BarcodeBackup..BarcodeBak SELECT *,GETDATE() FROM Barcode (NOLOCK) WHERE MO_Code=@charMO and Isnull(Code,'')<>'VOID'
IF @@ERROR <> 0
BEGIN
RAISERROR('Error occurred, cannot BACKUP Barcode %d', 16, 1, @charMO)
ROLLBACK TRAN ClearBarcode
GOTO RESUME_NEXT
END
-----delete barcode
DELETE Barcode WHERE MO_Code=@charMO
IF @@ERROR <> 0
BEGIN
RAISERROR('Error occurred, cannot delete Barcode %d', 16, 1, @charMO)
ROLLBACK TRAN ClearBarcode
GOTO RESUME_NEXT
END
------delete mo_bundle
DELETE MO_Bundle WHERE MO_Code=@charMO
IF @@ERROR <> 0
BEGIN
RAISERROR('Error occurred, cannot delete MO_Bundle %d', 16, 1, @charMO)
ROLLBACK TRAN ClearBarcode
GOTO RESUME_NEXT
END
------delete mo_bundle_range
DELETE MO_Bundle_Range WHERE MO_Code=@charMO
IF @@ERROR <> 0
BEGIN
RAISERROR('Error occurred, cannot delete MO_Bundle_Range %d', 16, 1, @charMO)
ROLLBACK TRAN ClearBarcode
GOTO RESUME_NEXT
END
-----update Clear_Barcode
UPDATE Clear_Barcode SET Finished=1,ClearedDateTime =getdate() WHERE ClearID=@intClearID
IF @@ERROR <> 0
BEGIN
RAISERROR('Error occurred, cannot update ClearBarcode %d', 16, 1, @intClearID)
ROLLBACK TRAN ClearBarcode
GOTO RESUME_NEXT
END
COMMIT TRAN ClearBarcode
IF @intClearCount % 10=0
BEGIN
DUMP TRAN PTP WITH NO_LOG
DUMP TRAN BarcodeBackup WITH NO_LOG
END
RESUME_NEXT:
FETCH NEXT FROM csClearBarcode
INTO
@intClearID,@charMO
END
DUMP TRAN PTP WITH NO_LOG
DUMP TRAN BarcodeBackup WITH NO_LOG
EXIT_PROC:
CLOSE csClearBarcode
DEALLOCATE csClearBarcode
RETURN 0
ERROR_PROC:
CLOSE csClearBarcode
DEALLOCATE csClearBarcode
RETURN -1
GO