--定义游标,循环处理数据
declare @id varchar(3)
declare #tb cursor for select id from test
open #tb
fetch next from #tb into @id
while @@fetch_status=0
begin
--字符串添加处理
declare @p varbinary(16)
select @p=textptr(detail) from test where id=@id
updatetext test.detail @p @postion 0 @s_str
fetch next from #tb into @id
end
close #tb
deallocate #tb
insert into [user]
select 1,'a'
union all select 2,'b'
union all select 3,'c'
insert into [order]
select 1,1
union all select 2,0
union all select 3,1
go
--处理的存储过程
CREATE PROCEDURE spUpdateUserLog
@StrLog text,
@State int
AS
--定义游标,循环处理数据
declare @uid int
declare #tb cursor for select a.uid from [user] a join [order] b on a.uid=b.uid
where state=@state
open #tb
fetch next from #tb into @uid
while @@fetch_status=0
begin
--字符串添加处理
declare @p varbinary(16)
select @p=textptr(UserLog) from [user] where uid=@uid
updatetext [user].UserLog @p null 0 @StrLog
fetch next from #tb into @uid
end
close #tb
deallocate #tb
go
--调用示例:
exec spUpdateUserLog '123',1
--显示处理结果
select * from [user]
go
--删除测试环境
drop table [user],[order]
drop proc spUpdateUserLog
/*--测试结果
uid UserLog
----------- ----------
1 a123
2 b
3 c123