Proc to Rename a file

USE master
GO
/****** Object: StoredProcedure [dbo].[sp_RenameFile] Script Date: 02/27/2007 14:28:04 ******/IF CHARINDEX('SQL Server 2005', @@Version) > 0
 BEGIN
 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RenameFile]') AND type in (N'P', N'PC'))
 BEGIN
 PRINT 'Dropping procedure [sp_RenameFile] - SQL 2005'
 DROP PROCEDURE [dbo].[sp_RenameFile]
 END
 END
ELSE
 BEGIN
 IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_RenameFile' AND type in (N'P', N'PC'))
 BEGIN
 PRINT 'Dropping procedure [sp_RenameFile] - SQL 2000'
 DROP PROCEDURE [dbo].[sp_RenameFile]
 END
 END

GO
/****** Object: StoredProcedure [dbo].[sp_RenameFile] Script Date: 02/27/2007 14:28:15 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_RenameFile]
(
 @Folder VARCHAR(128) = NULL,
 @FileName VARCHAR(64) = NULL,
 @NewFileName VARCHAR(64) = NULL,
 @DateTimeType VARCHAR(16) = NULL,
 @Debug BIT = 0
)

-- --------------------------------------------------------------------------------------------------
-- Procedure : [sp_RenameFile]
-- Description : To rename a file.
-- Requires xp_cmdshell be enabled.
-- Parameters Folder The folder where the file exists.
-- REQUIRED.
-- FileName The file to be renamed.
-- REQUIRED.
-- NewFileName The new file name
-- OPTIONAL - If not specified (NULL or empty / space) -
-- add a DateTime literal (_yyyymmddhhnnss) to the filename,
-- as in filename.ext becomes filename_yyyymmddhhnnss.ext
-- DateTimeType One of (not case sensitive):
-- CREATED Created DateTime
-- MODIFIED Modified DateTime
-- NULL / '' NULL or spaces.
-- Value used to generate the DateTime literal for the
-- default generated filename.
-- OPTIONAL - If not specified (NULL or empty / space) -
-- use GETDATE().
-- Debug Switch to determine if debugging information should be output
-- OPTIONAL - defaults to 0 (No).
--
-- Modification Log
-- When Who Description
-- 03/31/2008 Simon Facer Original Version
-- 05/14/2008 Simon Facer Added logic to use Created or Modified file dates,
-- added @DateTimeType parameter.
-- --------------------------------------------------------------------------------------------------

AS

BEGIN

 DECLARE @ExtIdx INT
 DECLARE @OS_Cmd VARCHAR(1024)
 DECLARE @CmdShell INT
 DECLARE @Msg VARCHAR(1024)
 DECLARE @FileDateTime DATETIME

 SET NOCOUNT ON

 IF @Debug = 1
 BEGIN
 SELECT 'Parameters',
 @Folder AS Folder,
 @FileName AS [FileName],
 @NewFileName AS NewFileName,
 @DateTimeType AS DateTimeType,
 @Debug AS Debug
 END


 -- ******************************************************************************************
 -- Validate the passed parameters
 IF ( @Folder IS NULL ) OR
 ( LTRIM(RTRIM(@Folder)) = '' )
 BEGIN
 SELECT 'Folder must be passed in.'
 RAISERROR ('Folder must be passed in', 16, 1)
 RETURN
 END
 IF ( @FileName IS NULL ) OR
 ( LTRIM(RTRIM(@FileName)) = '' )
 BEGIN
 SELECT 'File Name must be passed in.'
 RAISERROR ('File Name must be passed in', 16, 1)
 RETURN
 END
 IF @DateTimeType IS NOT NULL AND
 @DateTimeType != '' AND
 UPPER(@DateTimeType) != 'MODIFIED' AND
 UPPER(@DateTimeType) != 'CREATED' 
 BEGIN
 SELECT 'DateTimeType must be ''Created'' or ''Modified'' if entered.'
 RAISERROR ('Invalid value for DateTimeType, must be ''Created'' or ''Modified'' if entered.', 16, 1)
 RETURN
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Add a trailing '\' to the Folder Name if necessary.
 IF RIGHT(@Folder, 1) != '\'
 BEGIN
 SELECT @Folder = @Folder + '\'
 END
 -- ******************************************************************************************
 
 -- ******************************************************************************************
 -- Create the # Temp table for the DIR command result
 CREATE TABLE #DirOutput
 (
 FileID INT IDENTITY(1, 1) NOT NULL,
 FileDate DATETIME NULL,
 DirResult VARCHAR(512) NULL
 )
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Get the DIR results into the table
 SELECT @OS_Cmd = 'DIR ' + 
 CASE 
 WHEN UPPER(@DateTimeType) = 'MODIFIED' THEN '/TW ' 
 WHEN UPPER(@DateTimeType) = 'CREATED' THEN '/TC ' 
 ELSE ''
 END +
 ' "' + @Folder + @FileName

 IF @Debug = 1
 BEGIN
 PRINT 'DIR Command ' + @OS_Cmd
 END

 INSERT #DirOutput ( DirResult )
 EXECUTE master.dbo.xp_cmdshell @OS_Cmd
 -- ******************************************************************************************

 IF @Debug = 1
 BEGIN
 PRINT 'Completed DIR Command'
 SELECT 'DIR Output', *
 FROM #DirOutput
 END

 -- ******************************************************************************************
 -- If the file doesnt exist, exit ...
 IF EXISTS (SELECT *
 FROM #DirOutput
 WHERE DirResult LIKE '%File Not Found%')
 BEGIN
 SELECT @Msg = '>>> File ''' + @Folder + @FileName + ''' Not Found'
 PRINT @Msg

 GOTO TheEnd
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Identify the date time value for the new file name
 IF ( @NewFileName IS NULL) OR
 ( LTRIM(RTRIM(@NewFileName)) = '' ) 
 BEGIN
 IF ( @DateTimeType IS NULL) OR
 ( LTRIM(RTRIM(@DateTimeType)) = '' )
 BEGIN
 SELECT @FileDateTime = GETDATE()
 END
 ELSE
 BEGIN
 -- If the Modified or Created was specified as the FileDateTime value,
 -- retrieve it from the DIR command output
 DELETE #DirOutput
 WHERE RTRIM(DirResult) NOT LIKE ('%' + @FileName)
 OR DirResult IS NULL
 UPDATE #DirOutput
 SET FileDate = CAST((LTRIM(RTRIM(SUBSTRING(DirResult, 1, 22))) +
 CASE 
 WHEN UPPER(RIGHT((LTRIM(RTRIM(SUBSTRING(DirResult, 1, 22)))), 1)) = 'A'
 THEN 'M'
 WHEN UPPER(RIGHT((LTRIM(RTRIM(SUBSTRING(DirResult, 1, 22)))), 1)) = 'P'
 THEN 'M'
 ELSE ''
 END) AS DATETIME)
 SELECT @FileDateTime = FileDate
 FROM #DirOutput

 IF @Debug = 1
 BEGIN
 SELECT @Msg = 'File Date Time : ' + CONVERT(VARCHAR(32), @FileDateTime, 101) + ' ' + CONVERT(VARCHAR(32), @FileDateTime, 108) 
 PRINT @Msg
 SELECT 'Calculated Date', *
 FROM #DirOutput
 END

 END
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- If the NewFileName wasn't passed, construct the value to include the DATETIME literal
 IF ( @NewFileName IS NULL) OR
 ( LTRIM(RTRIM(@NewFileName)) = '' ) 
 BEGIN
 SELECT @NewFileName = @FileName

 SELECT @ExtIdx = LEN(@NewFileName) - CHARINDEX('.', (REVERSE(@NewFileName)))
 SELECT @NewFileName = SUBSTRING(@NewFileName, 1, @ExtIdx) + '_' + SUBSTRING((REPLACE(REPLACE(REPLACE((CONVERT(VARCHAR(32), @FileDateTime, 120)), '-', ''), ' ', ''), ':', '')), 1, 12) + SUBSTRING(@NewFileName, (@ExtIDX + 1), 999)

 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Rename the file
 SELECT @OS_Cmd = 'RENAME "' + @Folder + @FileName + '" "' + @NewFileName + '"'

 IF @Debug = 1
 BEGIN
 PRINT 'Rename Command ' + @OS_Cmd
 SELECT 'Rename Command', @OS_Cmd
 END

 EXECUTE master.dbo.xp_cmdshell @OS_Cmd

 IF @Debug = 1
 BEGIN
 PRINT 'Rename Completed'
 END
 -- ******************************************************************************************

TheEnd:
 RETURN 0

END

GO

IF CHARINDEX('SQL Server 2005', @@Version) > 0
 BEGIN
 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RenameFile]') AND type in (N'P', N'PC'))
 BEGIN
 PRINT 'Procedure Created [sp_RenameFile] - SQL 2005'
 END
 END
ELSE
 BEGIN
 IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_RenameFile' AND type in (N'P', N'PC'))
 BEGIN
 PRINT 'Procedure Created [sp_RenameFile] - SQL 2000'
 END
 END

GO