从存储过程返回游标

Question

您好，我需要从 STORED PROCEDURE 返回一个 CURSOR 我是这样处理的

create proc pps @return_cursor cursor VARYING OUTPUT As
DECLARE cursor_name CURSOR FOR
SELECT id FROM table_name

现在我的问题是捕获返回的 Cursor

DECLARE cur_ret  CURSOR 

cur_ret = 执行pps

但执行方法给出错误

我的问题是如何从存储过程返回游标并捕获它

Answer 1

查看在输出参数中使用光标数据类型

Answer 2

我发现可以这样做

创建 proc pps @ret_cur 游标
改变输出作为 DECLARE @CURx CURSOR
SET @CURx= 光标用于选择 id FROM
用户1

声明@CrsrVar游标；

执行 PPS @ret_cur=@CrsrVar 输出

Answer 3

请参阅链接中的“SQL Server 游标作为存储过程的输出”部分 https://www.mssqltips.com/sqlservertip/6308/ Different-ways-to-write-a-cursor-in-sql-server/

USE tempdb
GO

-- Cursor as an output of a stored procedure
CREATE PROCEDURE dbo.usp_cursor_db
@cursor_db CURSOR VARYING OUTPUT
AS
BEGIN
   DECLARE
      @database_id INT, 
      @database_name   VARCHAR(255);

   SET @cursor_db = CURSOR
   FOR SELECT 
         database_id, name
      FROM sys.master_files;

   OPEN @cursor_db;

END
GO

--从存储过程检索游标结果集输出的代码

-- Code to retrieve the cursor resultset output from the stored procedure
DECLARE 
    @cursor_db CURSOR

DECLARE
    @database_id INT, 
    @database_name   VARCHAR(255);

EXEC dbo.usp_cursor_db @cursor_db = @cursor_db OUTPUT

FETCH NEXT FROM @cursor_db INTO 
   @database_id, @database_name;

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT @database_name + ' id:' + CAST(@database_id AS VARCHAR(10));

   FETCH NEXT FROM @cursor_db INTO 
      @database_id, 
      @database_name;
END;

CLOSE @cursor_db;

DEALLOCATE @cursor_db;
GO