/*****************************************************************************************************************
在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'
