SQLServerCentral Article

Using Comments to Quickly Test CTEs

,

Consider the following SQL:

WITH
People AS (
 SELECT 1 ID, 'Joe'    FirstName, 'Smith'  LastName, 21 Age, 'Male'   Gender UNION ALL
 SELECT 2 ID, 'John'   FirstName, 'Smit'   LastName, 32 Age, 'Male'   Gender UNION ALL
 SELECT 3 ID, 'Jo'     FirstName, 'Schmit' LastName, 43 Age, 'Female' Gender UNION ALL
 SELECT 4 ID, 'Joanne' FirstName, 'Smith'  LastName, 54 Age, 'Female' Gender UNION ALL
 SELECT 5 ID, 'Juan'   FirstName, 'Smithe' LastName, 65 Age, 'Male'   Gender
)
,
OldPeople AS (
 SELECT * FROM People WHERE Age > 50
)
,
Men AS (
 SELECT * FROM People WHERE Gender = 'Male'
)
SELECT
 o.*
FROM
 Men AS m
 INNER JOIN OldPeople AS o
   ON m.ID = o.ID

Suppose you want to test the results from each of the Common Table Expressions (CTEs) one at a time. Let’s start with the first one.  You could insert the following right after the CTE:

 
select * from People /*
 

The end of this line contains the start of a block comment, which disables the rest of the query below it.  Before we can run this new query, we must close the block comment at the end of the file.  However, if we simply use the */ sequence to close it, we can no longer easily comment-out our new CTE query to see the results of the original query.

If we tried, we would see this error:

Msg 102, Level 15, State 1, Line 24

Incorrect syntax near '/'.

Instead, let’s use a combination of line and block comments:


--*/

OK, so now we have this:

WITH
People AS (
 SELECT 1 ID, 'Joe'    FirstName, 'Smith'  LastName, 21 Age, 'Male'   Gender UNION ALL
 SELECT 2 ID, 'John'   FirstName, 'Smit'   LastName, 32 Age, 'Male'   Gender UNION ALL
 SELECT 3 ID, 'Jo'     FirstName, 'Schmit' LastName, 43 Age, 'Female' Gender UNION ALL
 SELECT 4 ID, 'Joanne' FirstName, 'Smith'  LastName, 54 Age, 'Female' Gender UNION ALL
 SELECT 5 ID, 'Juan'   FirstName, 'Smithe' LastName, 65 Age, 'Male'   Gender
)
select * from People /*
,
OldPeople AS (
 SELECT * FROM People WHERE Age > 50
)
,
Men AS (
 SELECT * FROM People WHERE Gender = 'Male'
)
SELECT
 o.*
FROM
 Men AS m
 INNER JOIN OldPeople AS o
   ON m.ID = o.ID
--*/

We can now comment-out the new CTE query or leave it enabled - either way works!

The only problem now is that we cannot simply add a similar query to select the values of OldPeople or Men without also adding an extra set of closing block comments at the end of the file.  This is because all block comments must have a matching set of open and close tokens.

So, if we want to make it easy to test each of the three CTEs one by one, we would need something like this:

WITH
People AS (
 SELECT 1 ID, 'Joe'    FirstName, 'Smith'  LastName, 21 Age, 'Male'   Gender UNION ALL
 SELECT 2 ID, 'John'   FirstName, 'Smit'   LastName, 32 Age, 'Male'   Gender UNION ALL
 SELECT 3 ID, 'Jo'     FirstName, 'Schmit' LastName, 43 Age, 'Female' Gender UNION ALL
 SELECT 4 ID, 'Joanne' FirstName, 'Smith'  LastName, 54 Age, 'Female' Gender UNION ALL
 SELECT 5 ID, 'Juan'   FirstName, 'Smithe' LastName, 65 Age, 'Male'   Gender
)
--select * from People /*
,
OldPeople AS (
 SELECT * FROM People WHERE Age > 50
)
--select * from OldPeople /*
,
Men AS (
 SELECT * FROM People WHERE Gender = 'Male'
)
--select * from Men /*
SELECT
 o.*
FROM
 Men AS m
 INNER JOIN OldPeople AS o
   ON m.ID = o.ID
--*/--*/--*/

Presto!  Now if we want to select the values of any of these CTEs, all we have to do is uncomment the query that selects it.

Rate

3.72 (39)

Share

Share

Rate

3.72 (39)