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
Simulate the string processing function stuff
Complete the stuff processing of the ntext field in the table
-- Producer build 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 ~ Replace 9 with 'China'
Exec p_stuff 'tb', 'content', 8, 2, 'China ',''
Select * from TB
Drop table TB
--*/
Create proc p_stuff
@ Tbname sysname, -- Name of the table to be processed
@ Fdname sysname, -- text/ntext field name
@ Start Int = NULL, -- start position. null indicates appending data.
@ Length Int = NULL, -- replacement Length
@ STR nvarchar (4000), -- string to be inserted
@ Where nvarchar (1000) = ''-- condition of the record to be processed
As
If isnull (@ STR, '') ='' return
Declare @ s nvarchar (4000)
Set @ s ='
Declare @ ID int, @ PTR varbinary (16), @ start1 int
Declare TB cursor local
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 @ 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