Use Common Table Expressions when Unit Testing in TSQL

use-cte-when-unit-testing-in-tsql.txt

Use Common Table Expressions when Unit Testing in TSQL

TSQL, Microsoft's SQL dialect for SQLServer, is capable of executing CTE, also known as Common Table Expression. 
We can use these to separate concerns when setting up unit tests, like so, to determine whether a new process 
returns the same records as an old process:

WITH old_batch (found) as (SELECT FIELD AS found FROM OLD_QUERY_OR_PROCESS),
new_batch (found) as (SELECT FIELD AS found FROM NEW_QUERY_OR_PROCESS),
compare_old_to_new (found) as (select found from old_batch where found not in (select found from new_batch)),
compare_new_to_old (found) as (select found from new_batch where found not in (select found from old_batch)),
count_compare_1 (amount) as (select count(*) from compare_old_to_new),
count_compare_2 (amount) as (select count(*) from compare_new_to_old),
test_1 (result) as (select iif(0=amount,1,0) from count_compare_1),
test_2 (result) as (select iif(0=amount,1,0) from count_compare_2),
interpret_results (interpretation) as (select iif(
  (1=(select result from test_1))
  and (1=(select result from test_2))
  ),1,0)
),
verdict (verdict) as (select iif(1=interpretation,'True','False') from interpret_results)
select verdict from verdict;

This makes the individual steps very visible and easy to read.

Happy coding!