IF OBJECTPROPERTY(OBJECT_ID('dbo.CopyContentsFromFile'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.CopyContentsFromFile
    IF OBJECTPROPERTY(OBJECT_ID('dbo.CopyContentsFromFile'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.CopyContentsFromFile >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.CopyContentsFromFile >>>'
END
go
CREATE PROCEDURE CopyContentsFromFile (
        @SearchForFiles char(1) = 'I',
        @FilesMask varchar(30) = NULL
)
AS
/********************************************************************************/
/*	Created By :	Umachandar Jayachandran	(UC)				*/
/*	Created On :	16 Jan 2000						*/
/*	Description:	This SP is used to copy any modified files or new files */
/*			to the database. This uses the 'FmtFile.Cmd' script to  */
/*			replace any HTML tags.					*/
/********************************************************************************/
/*	Resources  :	http://www.umachandar.com/resources.htm         	*/
/********************************************************************************/
DECLARE @cmdstr nvarchar(4000), @tempcmd nvarchar(4000),
	@relativepath varchar(255), @filename varchar(255),
	@write datetime
CREATE TABLE #status([Filename] varchar(255) null, Messages varchar(255) null)

SELECT @tempcmd = Value,
	@SearchForFiles = UPPER(@SearchForFiles)
FROM Settings
WHERE Setting = 'Format File CMD Script'

DECLARE files CURSOR FOR
SELECT r.[RelativePath], r.[Filename], w.[Write]
FROM [My Resource Files] r Join WebTechFiles w
ON r.[Filename] = w.[Filename]
WHERE PATINDEX('<<%>>', r.[Filename]) = 0 And
	(@SearchForFiles  = 'I' or	/* To update new or modified contents */
		(@SearchForFiles = 'U' And Datediff ( mi, r.[Write] , w.[Write]) > 0 )) And
	(@SearchForFiles  = 'U' or	/* To initialize contents */
		(@SearchForFiles = 'I' And [Contents] Is Null)) And
	(@FilesMask IS NULL or		/* To filter specific files */
		r.[Filename] Like '%' + @FilesMask + '%')
ORDER BY r.[RelativePath] DESC, r.[Filename]

OPEN files
WHILE(1 = 1)
BEGIN
	FETCH files INTO @relativepath, @filename, @write
	IF @@FETCH_STATUS < 0 BREAK

	UPDATE [My Resource Files]
	SET [Contents] = '', [Write] = @write
	WHERE [Filename] = @filename And [RelativePath] = @relativepath

	SELECT @cmdstr = STUFF(@tempcmd, CHARINDEX(N'%s', @tempcmd), 2,
					@relativepath + '\' + @filename)
	INSERT #status (Messages) EXEC master..xp_cmdshell @cmdstr

	-- Insert the filename as a comment
	UPDATE #status
	SET [Filename] = @filename
	WHERE [Filename] IS NULL
END
DEALLOCATE files
SELECT * FROM #status
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.CopyContentsFromFile'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.CopyContentsFromFile To Public
    PRINT '<<< CREATED PROCEDURE dbo.CopyContentsFromFile >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.CopyContentsFromFile >>>'
go