CTE To Use Alternate Select When Select Returns No Rows
Here is the way I would do this, assuming the "do it all in one query" isn't just a pointless, artificial requirement.

SELECT cols INTO #x FROM dbo.Users WHERE UserID &gt; 5;

IF @@ROWCOUNT &gt; 0
  SELECT cols FROM #x;
ELSE
  SELECT cols FROM dbo.Users;


If the "do it all in one query" is a hard requirement and you can't use a #temp table, or you really have complex logic you've dumbed down from us and/or don't want to repeat, then you're going to have to pay for those choices in performance, I'm afraid. This looks like it should be quite efficient, but there are going to be more hits to the base table than most people would hope / expect:

;WITH TargetUsers AS 
(
  SELECT cols FROM dbo.Users WHERE UserID &gt; 5
),
AllUsers AS 
(
  SELECT cols FROM dbo.Users
  WHERE NOT EXISTS (SELECT 1 FROM TargetUsers)
    -- this will only return rows if the first CTE is empty
),
ComputeWithUsers AS
(
  SELECT cols FROM TargetUsers 
  UNION ALL 
  SELECT cols FROM AllUsers
)
...;


In fact if UserID is the primary key and the filter really is on UserID, this might be better as follows (but you'll have to check the plans to see, depends on a variety of factors):

;WITH TargetUserIDs AS 
(
  SELECT UserID FROM dbo.Users WHERE UserID &gt; 5
),
AllUserIDs AS 
(
  SELECT UserID FROM dbo.Users
  WHERE NOT EXISTS (SELECT 1 FROM TargetUsers)
    -- this will only return rows if the first CTE is empty
),
ComputeWithUsers AS
(
  SELECT UserID FROM TargetUserIDs 
  UNION ALL 
  SELECT UserID FROM AllUserIDs
)
...
-- and join to dbo.Users to get other columns necessary
-- deferring that to as late as possible


Hopefully your queries are using suboptimal practices due to simplification, and not like that in production, but please give these a read:


Bad habits to kick : using SELECT * / omitting the column list
Bad habits to kick : avoiding the schema prefix