数据库循环

数据库循环案例--修改OA的个人文件夹目录

declare @i int
declare @path varchar(255)
set @i = 2
while @i < 20
begin 
 
 select @path = user_path from basic_user where user_id = @i
 print cast(@i as nvarchar)  + '---' + @path
 if(LOWER(substring(@path, 1, 1)) = 's')
    update basic_user set user_path = 'H:/kingdee' + substring(@path, 3, len(@path)) where user_id = @i
 
 set @i = @i + 1
end

 

SQLServer 存储过程  获取extjs store fields 省得自己写了哈哈

DECLARE @tableName varchar(255), @fieldName varchar(255), @fields nvarchar(2555)
SET @tableName = 'sm_member'
SET @fields = ''
DECLARE cursor_getExtStoreFields CURSOR FOR
    SELECT syscolumns.name
    FROM syscolumns, systypes
    WHERE syscolumns.xusertype = systypes.xusertype  AND syscolumns.id = object_id(@tableName)
--打开游标
OPEN cursor_getExtStoreFields
--提取游标第一行
FETCH NEXT FROM cursor_getExtStoreFields INTO @fieldName
--循环提取游标内容
WHILE @@FETCH_STATUS=0
BEGIN
    SET @fields = @fields + '''' + @fieldName + '''' + ', '
    FETCH NEXT FROM cursor_getExtStoreFields INTO @fieldName
END
--关闭游标
CLOSE cursor_getExtStoreFields
--释放游标资源
DEALLOCATE cursor_getExtStoreFields
PRINT @fields