Simulates the string processing function stuff stored procedure, stuff the ntext field.

Stored Procedure | function | String if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_stuff] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_stuff]
Go

/*--ntext field Processing

Impersonation string handler function stuff
Completing stuff processing of ntext fields in a table
Note that the table requires a primary key (or an identity field) with the column name: ID, and the data type is int
If there is no primary key field, or other type, the corresponding need to modify the stored procedure

--Jiangjian 2004.07--*/

/*--Call Example

--Test data
CREATE table TB (ID int identity (1,1), content ntext)
Insert TB Select ' A;SD '
UNION ALL SELECT ' a;sdfkjas2qasdfdfsg45yhjhdfg45645a '

--Call the stored procedure to replace the 8th to 9th character with ' China '
EXEC p_stuff ' TB ', ' content ', 8, 2, ' China ', '
SELECT * FROM TB

DROP table TB
--*/

Create proc P_stuff
@tbname sysname,--The name of the table to process
@fdname sysname,--text/ntext field name
@start int=null,--start position, NULL indicates append data
@length the length of the Int=null,--replacement
@str nvarchar (4000),--the string to insert
@where nvarchar (1000) = ' '-condition of the record to be processed
As
If @str is null return
declare @s nvarchar (4000)
Set @s= '
declare @id int, @ptr varbinary, @start1 int

Declare TB cursor Local for
Select Id,start=datalength ([' + @fdname + '])/2
From [' + @tbname + ']
' +case isnull (@where, ') when ' then '
Else ' where ' + @where end+ '

Open TB
Fetch TB into @id, @start1
While @ @fetch_status =0
Begin
Select @ptr =textptr (content)
From [' + @tbname + ']
where id= @id

If @start is null or @start1 < @start
UPDATETEXT [' + @tbname + ']. [' + @fdname + '] @ptr null null @str
Else
Begin
Set @start1 = @start-1
UPDATETEXT [' + @tbname + ']. [' + @fdname + '] @ptr @start1 @length @str
End
Fetch TB into @id, @start1
End
Close TB
Deallocate TB
'
EXEC sp_executesql @s
, N ' @start int, @length int, @str nvarchar (4000) '
, @start, @length, @str
Go