----------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can check my BLOG here

-- Data approach
Select @vCols = Stuff((Select ',case when a.' 
	+ [name] + ' = b.' 
	+ [name] + ' then Cast(b.' + [name]
	+ ' as varchar(10)) else cast(b.'
	+ [name] + ' as varchar(max)) + ''(old)'' + '' '' + Cast(a.'
	+ [name] + ' as varchar(10)) + ''(new)'' end as '
	+ [name]
from sys.columns 
where Object_id = Object_id('vTable1') for XML Path('')),1,1,'')

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can check my BLOG here

DECLARE @nbsql VARCHAR(MAX)
      , @nSQL NVARCHAR(MAX)
      , @p_exportid INT
      , @max INT
      , @p_Reported TINYINT
      
SET NOCOUNT ON

DECLARE @tblExportID TABLE (ExportID INT, IsReported TINYINT);
DECLARE @tblExportOrig TABLE (ExportValue VARCHAR(MAX))
DECLARE @tblExportNew TABLE (ExportValue VARCHAR(MAX))

INSERT INTO @tblExportID 
  SELECT exportid, Isreported 
  FROM dbo.ref_ExportID rei 
  WHERE rei.ExportType = 'SomeValue' 
  AND rei.IsValid = 1

SET @p_exportid = (SELECT TOP 1(exportid) FROM @tblExportID)
SET @p_Reported = (SELECT TOP 1(exportid) FROM @tblExportID)
SET @max = (SELECT COUNT(*) FROM @tblExportID)

WHILE @max > 0
	BEGIN
        -- Retrieve the 'dynamic' sql from the table
		EXECUTE nb_SP_Export_Data_Thread @ProcessDate = '20101001', @exportid = @p_exportid, @nbsql = @nbsql OUTPUT
		SET @nSql = convert(nvarchar(max),@nbsql)
		-- Get the original data from the dynamic sql, put it into the table variable
		INSERT INTO @tblExportOrig EXECUTE SP_executesql @nsql
		-- Get the data from the new stored proc, put it into the second table variable
		INSERT INTO @tblExportNew EXECUTE SP_Export_GLExtract @ExportID = @p_exportid, @Reported = @p_Reported, @ProcessDate = '20101001'
		-- Maintain the control table
		DELETE FROM @tblExportID WHERE ExportID = @p_exportid
		SET @max = (SELECT COUNT(*) FROM @tblExportID)
		SET @p_exportid = (SELECT TOP 1(exportid) FROM @tblExportID)
		SET @p_Reported = (SELECT TOP 1(exportid) FROM @tblExportID)
		
		-- Firstly select the data that matches in the two tables (this should be the same rowcount
		-- as the original statement will return
		SELECT 'Match' 'Matching Data', @p_exportid 'Export ID', o.ExportValue 'OrigValue', n.ExportValue 'New Value' 
		FROM @tblExportOrig o
		INNER JOIN @tblExportNew n
		ON o.ExportValue = n.ExportValue
		ORDER BY 1,2;
		
		
		-- Next, 
		SELECT 'In Orig, not in New' 'Orig Table', @p_exportid 'Export ID', o.ExportValue 'OrigValue', n.ExportValue 'New Value' 
		FROM @tblExportOrig o
		INNER JOIN @tblExportNew n
		ON o.ExportValue = n.ExportValue 
		WHERE NOT exists (SELECT ExportValue FROM @tblExportNew)
		ORDER BY 1,2;
		
		-- Finally, what have we put into the new table, that never existed in the old table
		SELECT 'In New, not in Orig' 'New Table', @p_exportid 'Export ID', o.ExportValue 'OrigValue', n.ExportValue 'New Value' 
		FROM @tblExportOrig o
		INNER JOIN @tblExportNew n
		ON o.ExportValue = n.ExportValue
		WHERE NOT EXISTS (SELECT ExportValue FROM @tblExportOrig)
		ORDER BY 1,2;				
		
		DELETE FROM @tblExportOrig;
		DELETE FROM @tblExportNew;

	END
	SET NOCOUNT OFF