USE [li]
GO
/****** 对象: StoredProcedure [dbo].[ReplaceKeywordOther] 脚本日期: 05/30/2012 14:59:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ReplaceKeywordOther]
@COLUMN VARCHAR(100),
@TABLE VARCHAR(100),
@CONTENT VARCHAR(100)
AS
SET nocount ON
Declare @KEYWORD NVARCHAR(50),@NEWWORD NVARCHAR(50),@KEYWORDTYPE NVARCHAR(50)
Declare @CnsbDataMaxCount int,@KeywordMaxCount int
Declare @CnsbDataNumber int,@KeywordNumber int
Declare @ReplaceSql nvarchar(2000)
Declare @Count int
print getdate()
print 'Start'
truncate table li.dbo.ReplaceRecordTemp
select @KeywordMaxCount=max(id) from li.dbo.IllegalKeyWordTemp
set @KeywordNumber=1
while @KeywordNumber<=@KeywordMaxCount
begin
select @KEYWORD=keyword,@NEWWORD=newword,@KEYWORDTYPE=keywordtype from li.dbo.IllegalKeyWordTemp where id=@KeywordNumber
exec('insert into li.dbo.ReplaceRecordTemp(ResultID,TableName,ColumnName,Keyword,NewWord,KeywordType) select '+@COLUMN+','''+@TABLE+''','''+@COLUMN+''','''+@KEYWORD+''','''+@NEWWORD+''','''+@KEYWORDTYPE+''' FROM '+@TABLE+' With(Nolock) where Contains('+@CONTENT+','''+@KEYWORD+''')')
SET @KeywordNumber=@KeywordNumber+1
end
select @CnsbDataMaxCount=max(id) from li.dbo.ReplaceRecordTemp
set @CnsbDataNumber=1
set @Count=0
while @CnsbDataNumber<=@CnsbDataMaxCount
begin
select @ReplaceSql='update '+@TABLE+' set '+@CONTENT+'=replace('+@CONTENT+','''+keyword+''','''+newword+''') where '+@COLUMN+'='+cast(ResultID as nvarchar(10)) from li.dbo.ReplaceRecordTemp where id=@CnsbDataNumber
Print @ReplaceSql
exec @ReplaceSql
set @CnsbDataNumber=@CnsbDataNumber+1
set @Count=@Count+1
if @Count>=100
begin
print getdate()
print 'waitfor'
waitfor delay '0:05'
set @Count=0
end
end
insert into li.dbo.ReplaceRecord0601(ResultID,TableName,ColumnName,Keyword,NewWord,KeywordType) select ResultID,TableName,ColumnName,Keyword,NewWord,KeywordType from li.dbo.ReplaceRecordTemp
print getdate()
print 'End'
转载于:https://blog.51cto.com/497017/884662