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'