sql ntext 替换存储过程
[SQL]代码view source print?01create procedure replace_ntext?????????? 02??@table_name??? nvarchar(100),?? --目标表名?????? 03??@field_name??? nvarchar(100),?? --目标字段名?????? 04??@where_str??????? nvarchar(1000),?? --该表主键字段名? 如id=1????? 05??@old??? nvarchar(1000),??????? --需要替换的字符串?????? 06??@new??? nvarchar(1000)???????? --替换后的字符串????????? 07as?????08begin??09?????10declare @SqlStr nvarchar(4000)???? 11????set @SqlStr = 'declare @pos int, @len int, @count int? set @count=0? set @len=len('''+@old+''') '12????set @SqlStr = @SqlStr + ' select @pos = patINDEX(''%'+@old+'%'',['+@field_name+']) - 1'+' from ['+@table_name+'] where '+ @where_str 13????set @SqlStr = @SqlStr + ' while @pos>0 '?14????set @SqlStr = @SqlStr + ' begin'???15????set @SqlStr = @SqlStr + ' declare @ptrval binary(16)'??????16????set @SqlStr = @SqlStr + ' SELECT? @ptrval? =? TEXTPTR(['+@field_name+'])?? from ['+@table_name+'] where '+? @where_str 17????set @SqlStr = @SqlStr + ' UPDATETEXT ['+@table_name+'].['+@field_name+'] @ptrval? @pos? @len '''+ @new + ''''18????set @SqlStr = @SqlStr + ' select @pos = patINDEX(''%'+@old+'%'',['+@field_name+']) - 1'+' from ['+@table_name+'] where '+ @where_str 19????set @SqlStr = @SqlStr + ' set? @count=@count+1 '20????set @SqlStr = @SqlStr + ' end '21????set @SqlStr = @SqlStr + ' select @count'22??????--print @SqlStr 23????????execute sp_executesql?? @SqlStr??????????? 24end
