Create procedure USP_DelOldFiles @path varchar(25),@duration int
as
--Objective: To delete files older than certain period from a folder
--Usage example: 
--Exec USP_DelOldFiles 'c:\test',30 -- which deletes files older than todaydate-30
--Created by :MAK
--Created date: Jan 7,2003
--OS: windows 2000
declare @myquery varchar(1000)
declare @query varchar(1000)
declare @name varchar(100)
set @myquery = "exec master.dbo.xp_cmdshell 'dir "+ ltrim(rtrim(@path)) + "\*.* /a/od'"
print @query 

create table #Filenames (id int identity(1,1) ,name varchar(100)) 

insert #Filenames(name)
exec (@Myquery)
delete from #Filenames where substring(name,3,1) <> '/' or name is null or
substring(name,25,1) ='<' 

Declare mycursor cursor for 
select name from #Filenames where 
convert(datetime,left(name,10)) <= getdate()-@duration
open mycursor 

fetch next from mycursor into @name
while (@@fetch_status =0)
begin
set @query = 'exec master.dbo.xp_cmdshell "del '+@path+'\'+ ltrim(rtrim(substring(@name,40,59)))+'"'
--print @query
exec (@query)
fetch next from mycursor into @name
end
close mycursor 
deallocate mycursor 

drop table #Filenames