News for hackers. Stuff that matters.
Re:Ask HN: How do you test SQL? by intrasight on Wednesday February 01, 2023 @09:57AM (#34611306)
I'm a fan of CTEs too. Here's a pattern that I use in Sql Server for testing/debugging when using CTE pipelines. Same approach would work with "FOR JSON"

   declare @debug bit = 1;

   ;with cte1 as (
     select
       @debug AS Debug1,
       ...
   ),
   cte2 as (
     select 
       @debug AS Debug2,
       ... from cte1
   ),
   cte3 as (
     select 
        @debug AS Debug3,
        ... from cte2
   )
   select
     -- dump intermediate if debug
     (
       select * from cte1 where Debug1=1 for xml raw ('row'), root ('cte1'), type
     )
     ,(
       select * from cte2 where Debug2=1 for xml raw ('row'), root ('cte2'), type
     )
     ,(
       select * from cte3 where Debug3=1 for xml raw ('row'), root ('cte2'), type
     )
     -- final results
     ,(
         select ...
         from cte3 for xml raw ('row'), type
   )
   for xml raw ('results'), type;

There are no comments on this comment yet.