sqlserver 游标循环

Question

问题：怎么在游标下循环当前游标，比如实现外游标在内游标执行前释放资源，在内游标执行完之后打开游标，继续下面的循环

CREATE PROCEDURE 存储过程
    @ID INT,
    @SUM int
AS
BEGIN

DECLARE @FID int


SET @FID = @ID
SET @COUNT = @SUM+1

DECLARE cur2 CURSOR FOR  select语句

OPEN cur2

FETCH NEXT FROM cur2 INTO 循环字段

WHILE @@FETCH_STATUS = 0

BEGIN
       --操作

    IF (条件) > 0
    BEGIN
            CLOSE cur2 -- 关闭游标
                --内游标
        EXEC UpdateAndInsertData2 @ID = @FID,@SUM = @COUNT
        OPEN cur2 -- 重新打开游标
    END

    FETCH NEXT FROM cur2 INTO 循环字段
END

CLOSE cur2

DEALLOCATE cur2

END

Answer 1

解决办法：

```sql
CREATE PROCEDURE UpdateAndInsertData2
    @ID INT,
    @SUM int
AS
BEGIN

DECLARE @FID int
DECLARE @COUNT int
-- 声明游标
DECLARE @outer_cursor CURSOR

SET @FID = @ID
SET @COUNT = @SUM+1

-- 初始化游标
SET @outer_cursor = CURSOR FOR  select语句

OPEN @outer_cursor;

FETCH NEXT FROM @outer_cursor INTO 循环字段

WHILE @@FETCH_STATUS = 0

BEGIN
   --操作

    IF ( 条件判断) > 0
    BEGIN
        -- 嵌套内游标
        EXEC UpdateAndInsertData2 @ID = @FID,@SUM = @COUNT

    END


    FETCH NEXT FROM @outer_cursor INTO 循环字段
END

-- 关闭和释放游标
CLOSE @outer_cursor;
DEALLOCATE @outer_cursor;

END

```