SQL Server:游标仅在管理工作室中运行时首次返回记录

Question

我希望有人能帮忙！我正在尝试编写一个使用游标的存储过程。当我在管理工作室中对此进行测试时，sproc 仅在第一次运行时返回记录。如果我复制/粘贴到另一个查询窗口，它再次只在第一次返回记录。从 C# 运行时，sproc 按预期工作。

下面的查询演示了该问题。提前感谢！

-------------------------------------------------------------------
-- Query to reproduce problem with using cursor - management studio
-------------------------------------------------------------------
--
Declare     crsrTest Cursor For 
Select      TABLE_NAME 
From        INFORMATION_SCHEMA.TABLES; 
Declare     @TableName  varchar(128);
Open        crsrTest;  
-- 
-- This will only return records the *first* time when running in MS. 
-- Every time after that, I get the 'No records' message. 
-- 
if @@FETCH_STATUS <> 0 
Print 'No records';             
while   @@FETCH_STATUS = 0 begin 
Fetch   Next 
From    crsrTest
Into    @TableName;  
Print   @TableName;
end;
Close       crsrTest; 
Deallocate  crsrTest; 

Answer 1

您在OPEN和IF @@FETCH_STATUS <> 0之间缺少一个FETCH NEXT：

DECLARE crsrTest CURSOR FOR
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES; 
DECLARE @TableName VARCHAR(128);
OPEN crsrTest;  
FETCH NEXT FROM crsrTest INTO @TableName;
PRINT @TableName;
-- This will always return records
IF @@FETCH_STATUS <> 0 
PRINT 'No records';             
WHILE @@FETCH_STATUS = 0 
BEGIN
FETCH NEXT FROM crsrTest INTO @TableName;
PRINT @TableName;
END;
CLOSE crsrTest; 
DEALLOCATE crsrTest;

如果可以的话，远离光标(你似乎是新手，这就是我告诉的原因(。如果你是一个ORACLE的人，这并不像PL/SQL中的迭代那么快。