UpdateText 使用

 

Create   proc   [ dbo ] . [ UpdateTextColumn ]

@Table   varchar ( 100 ),

@Columns   varchar ( 200 ), -- eg:Column1,Column2,

@old   varchar ( 100 ),

@new   varchar ( 100 )

as

     set  nocount  on

     declare   @sql   nvarchar ( 2000 )

     declare   @Column   varchar ( 50 )

     declare   @cpos   int , @npos   int

     set   @cpos = 1 ;

     set   @npos = 1 ;

     set   @npos = charindex ( ' , ' , @Columns , @cpos );

     while ( @npos > 0 )

     begin

         set   @Column   =   substring ( @Columns , @cpos , @npos - @cpos );

         set   @cpos   =   @npos + 1

         set   @npos = charindex ( ' , ' , @Columns , @cpos );

        

         set   @sql   =   ' update  ' + @Table + '  set  ' + @Column + ' =replace(cast( ' + @Column + '  as varchar(8000)),@old,@new) where Datalength( ' + @Column + ' )<=8000 ' ;

         EXECUTE  sp_executesql  @Sql ,

                           N ' @old varchar(100),@new varchar(100) ' ,

                            @old ,

                            @new

         declare   @ptr   binary ( 16 ) , @offset   int , @dellen   int

        

         set   @dellen   =   len ( @old )

        

         set   @offset   =   1

         while   @offset >= 1

         begin

             set   @offset   =   0

             set   @sql   =   ' select     top 1 @offset = charindex( ''' + @old + '''  ,  ' + @Column + ' ), @ptr = textptr( ' + @Column + ' ) from  ' + @Table + '  where Datalength( ' + @Column + ' )>8000 and  ' + @Column + '  like  '' % ' + @old + ' % ''' ;

             EXEC  sp_executesql  @Sql ,N ' @offset int OUTPUT,@ptr binary(16) OUTPUT,@old varchar(100) ' ,

                                @offset  OUTPUT, @ptr  OUTPUT, @old ;

            if   @offset   >   0

             begin

                 set   @offset   =   @offset - 1

                 set   @sql = ' updatetext  ' + @Table + ' . ' + @Column + '  @ptr @offset @dellen @new ' ;

                 EXEC  sp_executesql  @Sql ,N ' @offset int ,@ptr binary(16),@dellen int,@new varchar(100) ' , @offset , @ptr , @dellen , @new ;

             end  

         end

end

go

使用方法:exec UpdateTextColumn @tbname,@column,@old,@new

转载于:https://www.cnblogs.com/craig/archive/2008/06/16/1222795.html