I have a lengthy query in SSMS that combines many CTEs, like "with cte1 as (~50 lines of sql), cte2 as (~50 lines of sql), cte3 as (~50 lines of sql)" etc. ... final select statement.
I would like to get the editor to treat the CTEs as blocks that can be collapsed. I have outlining toggled on in the Editor menu. Using Begin/End doesn't seem to work as the list of CTEs are treated as a single statement.
Are there any tricks or tips that might accomplish this?
Much thanks!
If you're using that many CTE's because, for example, due to the nature of the access/permissions you have to the datasource you cannot create table valued functions, then you may have luck looking at alternative IDEs for your query if you want to "Collapse the CTE" definitions.
If the goal is to just work with the query more efficiently while developing, there a few things that could help wrangle your code. I mentioned "creating table valued functions" because sometimes if you have that much logic going into one query, isolating the queries into persisted schema objects can help you do isolated testing of each part of the query, and reduces the number of lines in your "final select" etc.
You could consider temp tables if available to you. Even if they are not part of your deployed solution in production, they can help you examine your code :
DROP TABLE IF EXISTS #tempabc
SELECT 12345 [ABC]
INTO #tempabc
;WITH CTE_ABC AS
(
/*
SELECT 12345 [ABC]
*/
SELECT ABC FROM #tempabc
)
SELECT ABC FROM CTE_ABC
In this example, imagine the subquery was much longer. By using a temp table before the CTE chain, you can comment out your logic (which IS collapsible in SSMS) bringing your working number of lines in the CTE to 2 (one for the collapsed comment block, and one to select from the temp table).
Lastly, if you have a very large number of CTEs and you cannot utilize creating schema objects to store the subquery logic, you may find it easier to balance out your logic across both subqueries AND CTE definitions