Comparing Two Tables By SQL Stored Procedure

I have created a SQL Stored Procedure to compare any two tables based on Microsoft SQL Server 2005 new syntax EXCEPT and INTERCEPT. Basically, I used EXCEPT and UNION to get the result of differences between tables and INTERCEPT to get the same result of two tables.

In addition to that, the SP will compare two tables by specifying column fields and conditions and display the result by optional ORDER BY clause. Here is the SP:

CREATE procedure [dbo].[SP_CompareTables] (
 @table1 varchar(100),
 @table2 varchar(100),
 @table_colList varchar(3000) = NULL,
 @whereClause varchar(3000) = NULL,
 @orderByClause varchar(3000) = NULL,
 @difference0 int = 0
)
AS
  DECLARE @sql varchar(8000);
  DECLARE @colList varchar(3000);
BEGIN
  if ( @table_colList is null Or @table_colList = '' )
  begin
    set @colList = '*';
  end
  else
  begin
    set @colList = @table_colList;
  end
  if ( @difference0 = 0 )
  begin
   set @sql =REPLACE(REPLACE(REPLACE('
SELECT ''@table1'' AS TblName, *
FROM (
  SELECT @colList
 FROM @table1
 EXCEPT (
   SELECT @colList 
  FROM @table2)
) x
UNION ALL
SELECT ''@table2'' AS TblName, *
FROM (
  SELECT @colList
 FROM @table2
 EXCEPT (
   SELECT @colList
  FROM @table1)
) y', 
'@table1', @table1),
'@table2', @table2),
'@colList', @colList);
  end;
  else
  begin
    set @sql =REPLACE(REPLACE(REPLACE('
SELECT @colList
  FROM @table1
  INTERSECT (
    SELECT @colList 
 FROM @table2)', 
'@table1', @table1),
'@table2', @table2),
'@colList', @colList);
  end;
  if ( @whereClause is not null And len(@whereClause) > 0 )
  begin
    set @sql = REPLACE(REPLACE('
SELECT * FROM (@sql) v
WHERE @whereClause', 
'@sql', @sql),
'@whereClause', @whereClause);
  end
  if ( @orderByClause is not null And len(@orderByClause) > 0 )
  begin
    set @sql = REPLACE(REPLACE('@sql 
ORDER BY @orderByClause', 
'@sql', @sql),
'@orderByClause', @orderByClause);
  end;
  print @sql;
  exec(@sql);
  return 0;
END

To use this SP is very simple. For example, to compare two tables of [Employees] and [Employees_backup], you can just run the following script to compare two whole tables:

EXEC SP_CompareTables 'Employees', 'Employees_backup';

More examples by specifying columns, where clause and order clause:

EXEC SP_CompareTables 'Employees', 'Employees_backup', 'FirstName, LastName';
EXEC SP_CompareTables 'Employees', 'Employees_backup', 'FirstName, LastName', 
  'FirstName like ''D%'' AND BirthDate Between ''Jan 01, 1990'' AND ''Jul 30, 2008''';
EXEC SP_CompareTables 'Employees', 'Employees_backup', 'FirstName, LastName', 
  NULL, 'FirstName, BirthDate' , 1; -- get same results