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

      在SQL Server 2000的開發(fā)過程中有時會遇到要判斷和刪除系統(tǒng)級別的一些文件,這是通過SQL Server2000提供的一些系統(tǒng)函數(shù)可以非常方便的完成相應(yīng)的功能.下面是我寫的一個實(shí)列:

Author:黃山光明頂

mail:leimin@jxfw.com

version:1.0.0

date:2004-1-30

(如需轉(zhuǎn)載,請注明出處!)

*********************************************************************************************************/
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‘