Alter proc s_FilePoll
@Source varchar(50)
as
/*
@Source defines the type of file with which we are dealing
Here the types are hard coded but it might be better to hold information in a table


declare @t table (s varchar(100))
declare @FileName varchar(100)
declare @Data varchar(100) 

if @Source = 'Classic'
begin
	insert @t
	exec master..xp_cmdshell 'dir /B \\FileShare\ExternalDataSource\Classic\File_*.tab'
	delete @t
	where s is null
	or s not like 'File_%.tab'

	select @FileName = ''
	while @FileName < (select MAX(s) from @t)
	begin
		select @FileName = MIN(s) from @t where s > @FileName
		exec s_FileProcess	@Entity = 'Classic' ,
							@Action = 'InsertFile',
							@Status = 'FileDownloaded',
							@FileName = @FileName,
							@Data = @Data
	end
end
if @Source = 'NewWorld'
begin
	insert @t
	exec master..xp_cmdshell 'dir /B \\FileShare\ExternalDataSource\NewWorld\File_*.tab.gz'
	delete @t
	where s is null
	or s not like 'File_*.tab.gz'

	select @FileName = ''
	while @FileName < (select MAX(s) from @t)
	begin
		select @FileName = MIN(s) from @t where s > @FileName
		if SUBSTRING(@data, 26,2) = 'HR'
			select @data = left(@data,24)		-- hourly file
		else if SUBSTRING(@data, 19,2) = 'DY'
			select @data = left(@data,17)		-- daily file
		else 
		begin
			raiserror ('Invalid filename %s', 16, -1, @FileName)
			return
		end
		
		exec s_FileProcess	@Entity = 'OmnitureHermes', 
							@Action = 'InsertFile', 
							@Status = 'FileDownloaded', 
							@FileName = @FileName,
							@Data = @Data
	end
end
go