游标使用

/*scroll表示可随意移动游标指针（否则只能向前），dynamic表示可以读写游标（否则游标只读）*/
DECLARE E1cursor cursor scroll dynamic      /* 声明游标，默认为FORWARD_ONLY游标 */
FOR SELECT name FROM test
OPEN E1cursor                /* 打开游标 */
declare @name sysname
FETCH NEXT from E1cursor into @name   /* 读取第1行数据*/
WHILE @@FETCH_STATUS = 0     /* 用WHILE循环控制游标活动 */
BEGIN
  --FETCH NEXT from E1cursor   /* 在循环体内将读取其余行数据 */
print 'UserName: ' + @name
FETCH NEXT from E1cursor into @name 
update test set pass='123' where current of E1cursor/*current当前*/
END

CLOSE E1cursor               /* 关闭游标 */
DEALLOCATE E1cursor          /* 删除游标 */

--复制一列内容变成字符串
declare @s varchar(20),@ss varchar(8000)
set @ss=''
DECLARE E1cursor cursor scroll dynamic    
FOR select distinct typename from #abc where typename is not null
OPEN E1cursor   
FETCH NEXT from E1cursor into @s
WHILE @@FETCH_STATUS = 0    
BEGIN
set @ss=@ss+','+@s
FETCH NEXT from E1cursor into @s
END
CLOSE E1cursor             
DEALLOCATE E1cursor
print @ss