/*****************************************************************************************************************

      在SQL Server 2000的开发过程中有时会遇到要判断和删除系统级别的一些文件,这是通过SQL Server2000提供的一些系统函数可以非常方便的完成相应的功能.下面是我写的一个实列:

Author:黄山光明顶

mail:leimin@jxfw.com

version:1.0.0

date:2004-1-30

(如需转载,请注明出处!)

*********************************************************************************************************/
if exists (select * from sysobjects where id =
object_id(''''dbo.usp_DeleteFile'''') and sysstat & 0xf = 4)
 drop procedure usp_DeleteFile
GO
CREATE PROCEDURE usp_DeleteFile
 @filename varchar(255),
 @path     varchar(1024)
AS
BEGIN


 DECLARE @rc   int
 Declare @space_num int
 Declare @debug  int
 Declare @tempPath varchar(1024)
 Declare @tempdel varchar(1024)

 Select @rc=0
 Select @space_num=0

 Select @debug=0

 Select @tempPath=''''''''
 Select @tempdel=''''''''
 set nocount ON


/***************************************************************************
***********
  * Check whether @filename is null

****************************************************************************
**********/
 if  @filename is null
 begin
  PRINT ''''Please input file name''''
  select @rc=-90001
  return @rc
 end


/***************************************************************************
***********
  * Check whether @path is null

****************************************************************************
**********/
 if  @path is null
 begin
  PRINT ''''Please input delete file directory(exclude space)''''
  select @rc=-90002
  return @rc
 end


/***************************************************************************
***********
  * Check whether @path  includes space
  * if the OS is windows 2000 ,please confirm the path not includes space!!

****************************************************************************
**********/
 select @space_num=charindex('''' '''',@path)
 if  @space_num>0
 begin
  while charindex('''' '''',@path)>0
  begin
   select @tempPath=@tempPath+left(@path,charindex('''' '''',@path))
   select @path=ltrim(right(@path,len(@path)-charindex('''' '''',@path)))
  end

  select @temppath=@temppath+@path
  select
@temppath=stuff(replace(@temppath,''''\'''',''''"\"'''')+''''"'''',1,3,left(@temppath,2))+''''\''''+
@filename

  if @debug=1
  begin
   print @temppath
  end


 end

 SELECT @temppath=@path+''''\''''+@filename

 if @debug=1
  BEGIN
   print @temppath
  END


/***************************************************************************
***********
  * Create temp table to records file information

****************************************************************************
**********/
 Create table #fileexists(doesexist smallint,fileindir smallint,direxist
smallint)
 if @rc=0
 begin

  insert into #fileexists exec master..xp_fileexist @temppath
  if exists (select 1 from #fileexists where #fileexists.doesexist=1)
   begin
    select @tempdel=''''del ''''+@temppath
     if @debug=1
      BEGIN
       print @tempdel
      END
    exec  @rc=master..xp_cmdshell @tempdel,no_output
    if @rc<>0
     begin
         print ''''Deleting file faile,May be file in useing!''''
         drop table #fileexists
         select @rc=-90003
         return @rc
     end
    else
     begin
         print ''''Deleting file sucessfully!''''
     end

   end
  else
   begin
    print ''''Please check the directory and filename.the input file not
exist!''''
   end

 end

        drop table #fileexists
 return @rc
END
go
exec usp_DeleteFile ''''test.txt'''',''''c:\temp''''