mssql游标

1,使用过程返回游标

CREATE PROCEDURE P_PRO1
	@O_CUR_RESULT  CURSOR VARYING OUTPUT
AS
BEGIN
	SET @O_CUR_RESULT = CURSOR 
 	FORWARD_ONLY  STATIC    
          FOR   
             SELECT XXX
	
	OPEN @O_CUR_RESULT
END

2,使用游标

-- 声明
DECLARE @L_ID INT,@L_NAME
DECLARE cur CURSOR FOR
SELECT ID,NAME FROM XXX
-- 这里或者是执行一个过程返回的游标
DECLARE @CURSOR_Result CURSOR
EXEC P_PRO1 @O_CUR_RESULT = @CURSOR_Result OUTPUT
-- 打开
OPEN cur

-- 去记录
FETCH FROM cur INTO @L_ID,@L_NAME -- 这里或者是FETCH FROM @CURSOR_Result INTO xxx
-- 循环
WHILE @@FETCH_STATUS = 0
 BEGIN
   ...
 FETCH FROM cur INTO @L_ID,@L_NAME	
 END
-- 销毁
CLOSE cur
DEALLOCATE cur

还有一种过程中使用动态拼接游标，参考网络，但是没用过：

DECLARE @SQLS    NVARCHAR(4000)    --拼接sql语句  
SET @SQLS = 'select  top '+cast(@TOP as varchar(10))+' id,name from XXX'
 
SET @SQLS='DECLARE cur CURSOR FOR '+@SQLS  
 EXEC(@SQLS)  
 OPEN cur
 FETCH NEXT FROM cur INTO @l_ID,@L_NAME