CREATE PROCEDURE sp_filecompare

--DECLARE @result INT EXEC @result = sp_filecompare 'c:\test.txt','c:\test1.txt',0,1,0,1,0,0 SELECT @result
--DECLARE @result INT EXEC @result = sp_filecompare 'c:\test.txt','c:\test1.txt',0,0,0,0,0,0 SELECT @result

@file1 VARCHAR(256),
@file2 VARCHAR(256),
@verbose BIT = 0,
@binary_only BIT = 0,
@case_insensitive BIT = 0,
@ascii_compare BIT = 0,
@unicode_compare BIT = 0,
@no_whitespace BIT = 0

AS

DECLARE @error VARCHAR(256)

--Make sure there are no incompatible switches.
IF (@binary_only = 1 AND @ascii_compare = 1)
	BEGIN
		SELECT @error = '@binary_only and @ascii_compare are incompatible switches.'
		GOTO ERROR_HANDLE
	END

--Declare objects.
DECLARE @cmd VARCHAR(4000)

CREATE TABLE #results(
	ident INT IDENTITY(1,1) PRIMARY KEY,
	result VARCHAR(4000))

--Create compare command.
SELECT @cmd = 'FC ' 
SELECT @cmd = CASE WHEN @binary_only = 0 THEN @cmd ELSE @cmd + ' /B ' END
SELECT @cmd = CASE WHEN @case_insensitive = 0 THEN @cmd ELSE @cmd + ' /C ' END
SELECT @cmd = CASE WHEN @ascii_compare = 0 THEN @cmd ELSE @cmd + ' /L ' END
SELECT @cmd = CASE WHEN @unicode_compare = 0 THEN @cmd ELSE @cmd + ' /U ' END
SELECT @cmd = CASE WHEN @no_whitespace = 0 THEN @cmd ELSE @cmd + ' /W ' END
SELECT @cmd = @cmd + @file1 + ' ' + @file2

INSERT #results(result)
	EXEC master..xp_cmdshell @cmd

IF @@ERROR <> 0
	BEGIN
		SELECT @error = 'Error inserting into #results.'
		GOTO ERROR_HANDLE
	END

--Test results.  If not verbose, return 0 for success or 1 for failure.  Otherwise, return complete result.
IF @verbose = 0
	BEGIN
		IF (SELECT result FROM #results WHERE ident = 2) = 'FC: no differences encountered'
			BEGIN
				RETURN 0
			END
		ELSE
			BEGIN
				RETURN 1
			END
	END
ELSE
	BEGIN
		SELECT result FROM #results
	END

IF @@ERROR <> 0
	BEGIN
		SELECT @error = 'Error returning results.'
		GOTO ERROR_HANDLE
	END

DROP TABLE #results

RETURN 0

ERROR_HANDLE:

RAISERROR(@error,16,1)
RETURN 1

GO