title: SQL Server CTE select on multiple conditions
body: <p>Just wondering the right way of doing it, I can't use "if" in following situation, and I need to add more conditions on it as well.</p>
  
  <p>I can do that by adding union but it's not efficient and I don't want to check exists multiple times then select.</p>
  
  <p>Any OPTIMAL way to do it? Thanks</p>
  
  <p>I want to select based on what it matches.</p>
  
  <pre><code> declare @studentID varchar(50) = '1432166';
   declare @firstname varchar(50) = 'Hello';
   declare @emailAddress varchar(100) = 'abc@hello.com';
   declare @mobileNumber varchar(10) = '2312321'
  
   with byStudentID as 
   (
        select 
            U3l_referenceID,
            preferredfirstname, 
            studentID,
            emailAddress,
            mobileNumber
        from 
            [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] L with(nolock)
        where 
            studentID = @studentID
  ),
  byfNameEmailAdd as 
  (
      select 
          U3l_referenceID,
          preferredfirstname, 
          studentID,
          emailAddress,
          mobileNumber
      from 
          [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] L with(nolock)
      where 
          preferredfirstname = @firstname 
          and emailAddress = @emailAddress 
  ),
  byMobileNumber as  
  (
      select 
          U3l_referenceID,
          preferredfirstname, 
          studentID,
          emailAddress,
          mobileNumber
      from 
          [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] L with(nolock)
      where 
          mobileNumber = @mobileNumber 
          and emailAddress = @emailAddress 
  )
  IF Exists (select * from byStudentID)
      select * from byStudentID
  End
  else IF Exists (Select * from byfNameEmailAdd)
      select * from byfNameEmailAdd
  End
  else if Exists (Select * from byMobileNumber)
      select  * from byMobileNumber
  End
  </code></pre>