Question

sql server中带游标的存储过程第一次执行可以显示结果集，但第二次后就不可以了，why？

CREATE PROCEDURE stu_cursor
@stu_cursor CURSOR VARYING OUTPUT
AS
SET @stu_cursor=CURSOR FORWARD_ONLY STATIC FOR
SELECT *
FROM xs
OPEN @stu_cursor
go
DECLARE @MyCursor CURSOR
EXECUTE stu_cursor @stu_cursor=@MyCursor OUTPUT
WHILE(@@FETCH_STATUS=0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO

Answer 1

就是，为什么啊，因为@@FETCH_STATUS是全局变量
WHILE前面添加FETCH NEXT FROM @MyCursor

Answer 2

CREATE PROCEDURE stu_cursor
@stu_cursor CURSOR VARYING OUTPUT
AS
SET @stu_cursor=CURSOR FORWARD_ONLY STATIC FOR
SELECT *
FROM xs
OPEN @stu_cursor
-- 这里的go去掉-------------
DECLARE @MyCursor CURSOR
EXECUTE stu_cursor @stu_cursor=@MyCursor OUTPUT
WHILE(@@FETCH_STATUS=0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO