Delete files older than n-days via T-SQL

USE Admin
GO

IF EXISTS (SELECT [name] FROM dbo.sysobjects WHERE [name] = 'usp_Admin_Delete_Files_By_Date' AND TYPE = 'P')
    DROP PROCEDURE dbo.usp_Admin_Delete_Files_By_Date
GO

CREATE PROCEDURE dbo.usp_Admin_Delete_Files_By_Date (@SourceDir varchar(1024), @SourceFile varchar(512), @DaysToKeep int)
-- EXEC Admin.dbo.usp_Admin_Delete_Files_By_Date @SourceDir = '\\FooServer\BarShare\'
--                             , @SourceFile = 'FooFile_*'
--                             , @DaysToKeep = 3

AS

/******************************************************************************
**
**    Name: usp_Admin_Delete_Files_By_Date.sql
**
**    Description: Delete files older than X-days based on path & extension.
**
**        Depending on the output from xp_msver, we will execute either a
**        Windows 2000 or Windows 2003 specific INSERT INTO #_File_Details_02
**        operation as there is a small difference in the FOR output between
**        Windows 2000 and 2003 (Operating system versions).
**
**    Return values: 0 - Success
**            -1 - Error
**
**    Author: G. Rayburn
**
**    Date: 03/26/2007
**
**    Depends on: xp_cmdshell access to @SourceDir via SQLAgent account.
**
*******************************************************************************
**        Modification History
*******************************************************************************
**
**    Initial Creation: 03/26/2007 G. Rayburn
**
*******************************************************************************
**
******************************************************************************/SET NOCOUNT ON

DECLARE @CurrentFileDate char(10)
    , @OldFileDate char(10)
    , @SourceDirFOR varchar(255)
    , @FileName varchar(512)
    , @DynDelete varchar(512)
    , @ProcessName varchar(150)
    , @OSVersion decimal(3,1)
    , @Error int


SET @ProcessName = 'usp_Admin_Delete_Files_By_Date - [' + @SourceFile + ']'
SET @CurrentFileDate = CONVERT(char(10),getdate(),121)
SET @OldFileDate = CONVERT(char(10),DATEADD(dd,-@DaysToKeep,@CurrentFileDate),121)
SET @SourceDirFOR = 'FOR %I IN ("' + @SourceDir + @SourceFile + '") DO @ECHO %~nxtI'
SET @Error = 0


-- Get Windows OS Version info for proper OSVer statement block exec.
CREATE TABLE #_OSVersion
    ( [Index] int
    , [Name] varchar(255)
    , [Internal_Value] varchar(255)
    , [Character_Value] varchar(255) )

INSERT INTO #_OSVersion
    EXEC master..xp_msver 'WindowsVersion'

SET @OSVersion = (SELECT SUBSTRING([Character_Value],1,3) FROM #_OSVersion)



-- Start temp table population(s).
CREATE TABLE #_File_Details_01
    ( Ident int IDENTITY(1,1)
    , Output varchar(512) )

INSERT INTO #_File_Details_01
    EXEC master..xp_cmdshell @SourceDirFOR

CREATE TABLE #_File_Details_02
    (Ident int
    , [TimeStamp] datetime
    , [FileName] varchar(255) )


-- OS Version specifics.
IF @OSVersion = '5.0'
    BEGIN -- Exec Windows 2000 version.
        INSERT INTO #_File_Details_02
            SELECT Ident
                , CONVERT(datetime, LEFT(CAST(SUBSTRING([Output],1,8) AS datetime),12)) AS [TimeStamp]
                , SUBSTRING([Output],17,255) AS [FileName]
                FROM #_File_Details_01

            WHERE [Output] IS NOT NULL
            ORDER BY Ident
    END

IF @OSVersion = '5.2'
    BEGIN -- Exec Windows 2003 version.
        INSERT INTO #_File_Details_02
            SELECT Ident
                , CONVERT(char(10), SUBSTRING([Output],1,10), 121) AS [TimeStamp]
                , SUBSTRING([Output],21,255) AS [FileName]
                FROM #_File_Details_01
    
            WHERE [Output] IS NOT NULL
            ORDER BY Ident
    END



-- Start delete ops cursor.
DECLARE curDelFile CURSOR
READ_ONLY
FOR

    SELECT [FileName]
        FROM #_File_Details_02
    WHERE [TimeStamp] <= @OldFileDate

OPEN curDelFile

FETCH NEXT FROM curDelFile INTO @FileName
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN

        SET @DynDelete = 'DEL /Q "' + @SourceDir + @FileName + '"'

        EXEC master..xp_cmdshell @DynDelete

    END
    FETCH NEXT FROM curDelFile INTO @FileName
END

CLOSE curDelFile
DEALLOCATE curDelFile

DROP TABLE #_OSVersion
DROP TABLE #_File_Details_01
DROP TABLE #_File_Details_02
GO