Batch replacement of ntext fields in sqlserver (updatetext usage)

I. Problem description:
1. In SQL Server, the ntext/text/image field cannot be replaced by the replace function;
2. By convert field conversion, you can convert the ntext field to varchar (8000) and replace it with the Relpace function. However, this method is not applicable to ntext fields with a field length greater than 8000.
Ii. Problem Solving
The code for sorting out common stored procedures is as follows:
Copy codeThe Code is as follows:
CREATE procedure [dbo]. [Proc_UpdateNTextField]
@ TargetTable nvarchar (1000), -- target table name
@ TargetField nvarchar (1000), -- target field name
@ PKField nvarchar (1000), -- Name of the primary key field of the table
@ Otxt nvarchar (1000), -- string to be replaced
@ Ntxt nvarchar (1000) -- replaced string
As
Begin
Declare @ SqlStr nvarchar (4000)
Set @ SqlStr = 'Clare @ txtlen int'
Set @ SqlStr = @ SqlStr + 'set @ txtlen = len (''' + @ otxt + ''')'
Set @ SqlStr = @ SqlStr + 'Clare @ pos int'
Set @ SqlStr = @ SqlStr + 'set @ pos = 0'
Set @ SqlStr = @ SqlStr + 'maid cursor local fast_forward for select'
Set @ SqlStr = @ SqlStr + @ PKField + ', textptr (' + @ TargetField + ') from '+ @ TargetTable + 'where' + @ TargetField +' like ''% '+ @ otxt +' % '''
Set @ SqlStr = @ SqlStr + 'Clare @ ptr binary (16 )'
Set @ SqlStr = @ SqlStr + 'Clare @ id char (32 )'
Set @ SqlStr = @ SqlStr + 'open curs'
Set @ SqlStr = @ SqlStr + 'fetch next from curs into @ id, @ ptr'
Set @ SqlStr = @ SqlStr + 'while @ fetch_status = 0'
Set @ SqlStr = @ SqlStr + 'begin'
Set @ SqlStr = @ SqlStr + 'select @ pos = patindex (''% '+ @ otxt +' %'', ProductDesc) from ProductTemp where ProductID = @ id'
Set @ SqlStr = @ SqlStr + 'while @ pos> 0'
Set @ SqlStr = @ SqlStr + 'begin'

Set @ SqlStr = @ SqlStr + 'set @ pos = @ pos-1'
Set @ SqlStr = @ SqlStr + 'updatetext '+ @ TargetTable +'. '+ @ TargetField +' @ ptr @ pos @ txtlen ''' + @ ntxt + ''''
Set @ SqlStr = @ SqlStr + 'select @ pos = patindex (''% '+ @ otxt +' %'', ProductDesc) from ProductTemp where ProductID = @ id'
Set @ SqlStr = @ SqlStr + 'end'
Set @ SqlStr = @ SqlStr + 'fetch next from curs into @ id, @ ptr'
Set @ SqlStr = @ SqlStr + 'end'
Set @ SqlStr = @ SqlStr + 'close curs'
Set @ SqlStr = @ SqlStr + 'destlocate curs'
EXECUTE sp_executesql @ SqlStr
End