I have created an involved
CTE, with no recursion, but with multiple selects being done as it processes the business logic of the requirement. Though, I have been advised that the logic needed for the scenario where no rows are returned, should instead to return all rows found from one of the middle
CTEs select instead of the final operation.
How can this be achieved?
Note the following example is basic interpretation of my process and the operation in question occurs after multiple cascaded CTE selects are done an essentially chained together.
Example (as found on SQLFiddle)
In a User Table there are two records where the ids of
2. The first
TargetUsers will return no rows requiring the the third CTE,
ComputeWithUsers, to use the
WITH TargetUsers AS ( SELECT * FROM Users WHERE UserId > 5 ) , AllUsers AS ( SELECT * FROM Users ) , ComputeWithUsers ( -- This CTE will determine it needs to use `AllUsers` and not `TargetUsers` )
Or is there a way to make the
TargetUsers select smart enough to extract all users when its main operation returns no rows?
SELECT filtered_set INTO #x; IF @@ROWCOUNT > 0 SELECT FROM #x ELSE SELECT FROM source;? Hopefully the filtered query is typically small or empty, and can use a seek and/or short circuit, but even if not, the worst possible case is two scans. With multiple references to stacked CTEs, the best possible case is (I think) three scans, and it could get worse, depending on the logic you simplified away. Many think a CTE is materialized, but it isn't, so multiple references usually result in multiple table accesses.