如果没有返回记录，如何避免执行游标语句
我有一个嵌套游标，其 select 语句有时会返回 0 条记录。
游标执行插入语句。
我希望如果查询返回 0 条记录，则不会执行插入语句。但无论如何它都会被执行。您能建议一种方法来避免这种情况吗？
declare nested_cursor CURSOR
FOR
SELECT MyRECORD
FROM MyTable
WHERE MyRECORD = @ID -- @ID is a variable defined in the main cursor
-- for some values of @iD ht above select statement may return zero records
OPEN nested_cursor
FETCH NEXT FROM nested_cursor INTO @NestedID
WHILE (@@fetch_status = 0)
BEGIN
INSERT STATEMENT -- HERE I HAVE THE PROBLEM , why this executes?
FETCH NEXT FROM nested_cursor INTO @NestedID
END
CLOSE nested_cursor
DEALLOCATE nested_cursor
更新：我通过检查 INSERT 语句之前的 @NestedId 是否为 null 找到了解决方法。
你有什么建议？添加此检查
WHILE (@@fetch_status = 0) and (@NestedID is not null)
还是有更好的技术？
I have a nested cursor whose select statement sometimes returns 0 records.
The cursor executes an insert statement.
I'd expect that the insert statement is not executed if the query returns 0 records. But it is executed anyway. Could you suggset a way to avoid this?
declare nested_cursor CURSOR
FOR
SELECT MyRECORD
FROM MyTable
WHERE MyRECORD = @ID -- @ID is a variable defined in the main cursor
-- for some values of @iD ht above select statement may return zero records
OPEN nested_cursor
FETCH NEXT FROM nested_cursor INTO @NestedID
WHILE (@@fetch_status = 0)
BEGIN
INSERT STATEMENT -- HERE I HAVE THE PROBLEM , why this executes?
FETCH NEXT FROM nested_cursor INTO @NestedID
END
CLOSE nested_cursor
DEALLOCATE nested_cursor
UPDATE: I found a workaround by checking if @NestedId is null just before the INSERT statement.
WHat do you suggest? to add this check in
WHILE (@@fetch_status = 0) and (@NestedID is not null)
or there is a better technique?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我添加了一条评论，但是，将在答案中重做选择，以便我可以使用代码格式使其更清晰。
I added a comment but, will redo the select in the answer so that I can use the code formatting to make it clearer.
它应该像你所展示的那样工作。正如其他人所说，光标似乎没有必要。
您可以尝试在 OPEN 语句后检查 @@CURSOR_ROWS 。作为替代方案，也许它返回行，但出乎意料的是，MyRecord 列为空，或者其他一些意外值？
It should be working as you've shown it. And as others have said, a cursor seems unnecessary.
You might try checking @@CURSOR_ROWS after your OPEN statement. As an alternative, maybe it is returning rows, but unexpectedly, the MyRecord column is null, or some other unexpected value?