/ ************************************************** *********************************************************** *************
Some files that sometimes encounter to judge and delete system levels during the development of SQL Server 2000, which is very convenient to complete the corresponding functionality through some system functions provided by SQL Server2000. Below is a list I wrote:
Author: Huangshan Guangmingding
Mail: leimin.ray@gmail.com
Version: 1.0.0
Date: 2004-1-30
(For reprint, please indicate the source!)
*********************************************************** *********************************************************** ***** / if exists (select * from sysobjects where id = object_id ( 'dbo.usp_DeleteFile') and sysstat & 0xf = 4) drop procedure usp_DeleteFileGOCREATE PROCEDURE usp_DeleteFile @filename varchar (255), @path varchar (1024) ASBEGIN 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 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 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, Direxistsmallint) 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_O utput 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 noteXist! 'End end drop table #fileexists return @rcendgoExec usp_deletefile'
