Cfrgtkky

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.

I can do that by adding union but it's not efficient and I don't want to check exists multiple times then select.

Any OPTIMAL way to do it? Thanks

I want to select based on what it matches.

 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

You can use UNION ALL to pull any results found by any method. Here I added a new [SearchType] field just so you can tell where the results came from:

 DECLARE @studentID VARCHAR(50) = '1432166';

 DECLARE @firstname VARCHAR(50) = 'Hello';

 DECLARE @emailAddress VARCHAR(100) = 'abc@hello.com';

 DECLARE @mobileNumber VARCHAR(10) = '2312321'



  SELECT 

  [SearchType] = 'StudentID',

  [U3l_referenceID],

  [preferredfirstname], 

  [studentID],

  [emailAddress],

  [mobileNumber]

    FROM [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] [L] WITH(NOLOCK)

    WHERE [studentID] = @studentID

UNION ALL

  SELECT 

  [SearchType] = 'FN.Email',

  [U3l_referenceID],

  [preferredfirstname], 

  [studentID],

  [emailAddress],

  [mobileNumber]

    FROM [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] [L] WITH(NOLOCK)

    WHERE [preferredfirstname] = @firstname 

        AND [emailAddress] = @emailAddress

UNION ALL

  SELECT 

  [SearchType] = 'MN.Email',

  [U3l_referenceID],

  [preferredfirstname], 

  [studentID],

  [emailAddress],

  [mobileNumber]

    FROM [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] [L] WITH(NOLOCK)

    WHERE [mobileNumber] = @mobileNumber 

        AND [emailAddress] = @emailAddress

If you only need one answer, just wrap the whole thing in a CTE and then select TOP 1 from it.

I just noticed that these are all coming from the same table, so you can use a single query with nested OR statements:

SELECT  [SearchType]    = 'StudentID'

    , [U3l_referenceID]

    , [preferredfirstname]

    , [studentID]

    , [emailAddress]

    , [mobileNumber]

FROM    [u3_data].[data].[ListData_3bf154c40aa84565b9bb08d58ffffff6] [L] WITH (NOLOCK)

WHERE ([studentID] = @studentID)

    OR

    (

        [preferredfirstname] = @firstname

        AND [emailAddress] = @emailAddress

    )

    OR

    (

        [mobileNumber] = @mobileNumber

        AND [emailAddress] = @emailAddress

    ) ;