CREATE procedure sp_replace_and_document @sourceDirName varchar(200), @destDirName varchar(200), @old_func varchar(100), @new_func varchar(100), @remark varchar(200), @strdate varchar(20), @coder varchar(20) as begin declare @sqlstmt varchar(300) declare @filename varchar(100) declare @sourceFileName varchar(200) declare @destFileName varchar(200) declare @countLines int set nocount on create table #files (filenm varchar(100)) set @sqlstmt = 'dir /B ' +@sourceDirName + '\*.*' Insert #files exec xp_cmdshell @sqlstmt declare c_files cursor for select filenm from #files where filenm is not null open c_files fetch next from c_files into @filename while (@@fetch_status = 0 ) begin set @sourceFileName = @sourceDirName + '\' + @filename set @destFileName = @destDirName + '\' + @filename truncate table textFile set @sqlstmt = 'BULK INSERT textFile From ' + '''' + @sourceFileName + '''' + ' WITH ( ROWTERMINATOR = ' + '''' + '\n' + '''' + ' ) ' exec (@sqlstmt) select @countLines = count(Line) from textFile print 'Working now on ' + @sourceFileName + '...' + 'Lines In File: ' + convert (varchar(10),@countLines) update textFile set line = replace ( line, @old_func, @new_func) + ' // ' + @coder + ': ' + @strdate + ' -- ' + @remark where CharIndex (@old_func, line) > 0 set @sqlstmt = ' bcp.exe textFile out ' + '"' + @destFileName + '"' + ' -c -C -q -T' exec xp_cmdShell @sqlstmt fetch next from c_files into @filename end close c_files deallocate c_files drop table #files set nocount off End GO