This sort of jogged my memory when I read it, and believe it or not, two days later I actually needed to do it. Here's my 'real world' example using table variables.

Basically, I have an sql statement that is contained within a table, and a stored proc retrieves it & replaces certain values depending on the value of the export_id. This is the way the system currently operates, and it returns x rows, depending on the export id

I have to recreate this "brilliant" concept within a series of stored procs, and wanted to capture the relevant data, then check it to make sure that the data returned is exactly the same. This is what I came up with:

DECLARE @nbsql VARCHAR(MAX)

, @nSQL NVARCHAR(MAX)

, @p_exportid INT

, @max-2 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-2 = (SELECT COUNT(*) FROM @tblExportID)

WHILE @max-2 > 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-2 = (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