|
/*****************************************************************************************************************
在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''''
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 