declare @ss ntext select @ss=field from table where ..... set @ss=@ss+'string' update table set field = @ss + 'string' where ……
在存储过程中定义text或ntext时提示数据类型无效
--通过一个临时表处理一下 declare @id int declare @ptrval binary(16) set @id=1 select id=identity(int,1,1),field into #1 from [table] where ...while @id<=(select max(id) from #1) begin select @ptrval=textptr(field) from #1 where id=@id updatetext #1.field @ptrval null 0 'string' set @id=@id+1 enddelete [table] where ... insert [table] select * from #1
--写的存储过程 create proc te as declare @p binary(16) declare tb cursor local for select textptr(field) from pcc open tb fetch tb into @p while @@fetch_status=0 begin if textvalid('pcc.field',@p)=1 updatetext pcc.field @p null 0 'string' fetch tb into @p end--执行存储过程,得结果 exec te select * from pcc
我测试的表结构为: create table pcc(field text)
--用游标试试: --假设表的主键为colA和colB CREATE PROCEDURE sp_AddToText AS declare cur_GetRecords cursor local for select colA,colB from 表 where ...... declare @ptrval binary(16) declare @colA ...... --colA的类型 declare @colB ...... --colB的类型 Open cur_GetRecords Fetch next from cur_GetRecords into @colA,@colB while @@Fetch_Status=0 begin select @ptrval=textptr(text字段) from 表 where colA=@colA and colB=@colB updatetext 表.field @ptrval null 0 '你要添加的文本' Fetch next from cur_GetRecords into @colA,@colB end Close cur_GetRecords Deallocate cur_GetRecords GO
select @ss=field from table where .....
set @ss=@ss+'string'
update table set field = @ss + 'string' where ……
declare @id int
declare @ptrval binary(16)
set @id=1
select id=identity(int,1,1),field into #1 from [table] where ...while @id<=(select max(id) from #1) begin
select @ptrval=textptr(field) from #1 where id=@id
updatetext #1.field @ptrval null 0 'string'
set @id=@id+1
enddelete [table] where ...
insert [table] select * from #1
--写的存储过程
create proc te
as
declare @p binary(16)
declare tb cursor local
for
select textptr(field) from pcc
open tb
fetch tb into @p
while @@fetch_status=0
begin
if textvalid('pcc.field',@p)=1
updatetext pcc.field @p null 0 'string'
fetch tb into @p
end--执行存储过程,得结果
exec te
select * from pcc
create table pcc(field text)
--假设表的主键为colA和colB
CREATE PROCEDURE sp_AddToText AS
declare cur_GetRecords cursor local for
select colA,colB from 表 where ......
declare @ptrval binary(16)
declare @colA ...... --colA的类型
declare @colB ...... --colB的类型
Open cur_GetRecords
Fetch next from cur_GetRecords into @colA,@colB
while @@Fetch_Status=0
begin
select @ptrval=textptr(text字段) from 表 where colA=@colA and colB=@colB
updatetext 表.field @ptrval null 0 '你要添加的文本'
Fetch next from cur_GetRecords into @colA,@colB
end
Close cur_GetRecords
Deallocate cur_GetRecords
GO