This stored procedure will process all files in a directory.
@ProcSp should be an SP that receives a file path and name and proceesses a file.
e.g.
	ftp the file to an ftp site
	bcp the file into a table and process

After processing the file the sp would often move it to an archive directory.



if exists (select * from sysobjects where id = object_id(N'[dbo].[s_ProcessAllFilesInDir]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ProcessAllFilesInDir]
GO

Create procedure s_ProcessAllFilesInDir
@FilePath	varchar(1000) ,
@FileMask	varchar(100) ,
@ProcSp		varchar(128) ,
@Debug 		int = 0
as
/*
exec s_ProcessAllFilesInDir
		@FilePath	= 'c:\' ,
		@FileMask	= '*.txt' ,
		@ProcSp		= '' ,
		@Debug		= 1
*/
	set nocount on
	
declare @File		varchar(128) ,
	@MaxFile	varchar(128) ,
	@cmd 		varchar(2000)
	
	create table #Dir (s varchar(8000))
	
	select	@cmd = 'dir /B ' + @FilePath + @FileMask
	insert #Dir exec master..xp_cmdshell @cmd
	
	delete #Dir where s is null or s like '%not found%'
	
	select	@File = '', @MaxFile = max(s) from #Dir
	while @File < @MaxFile
	begin
		select 	@File = min(s) from #Dir where s > @File
		
		select 	@cmd = @ProcSp + ' ''' + @FilePath + ''' , ''' + @File + ''''
		
		if @Debug = 1
			select 	@cmd
		else
			exec @cmd
		
	end
	drop table #Dir
go