Convert C# logic/lambda's to T-SQL I am trying to convert the following logic in C# to a SQL Server stored procedure:  

C# logic

// Filter by the search term if the first OR last name is null or empty
// || is an 'OR' operator that won't evaluate the next arguments if the first is true
if (String.IsNullOrEmpty(firstName) || String.IsNullOrEmpty(lastName))
{
        return result.Count(c =&gt; c.FirstName == searchString || 
                c.LastName == searchString || c.Brn == searchString);
}

// Filter by the first and last name if they are both present.
// Note: The first name doesn't have to be an exact match, but the last name does
// &amp;&amp; is an 'AND' operator that won't evaluate the next arguments if the first is false
return result.Count(c =&gt; c.FirstName.StartsWith(firstName) &amp;&amp; c.LastName == lastName);


This is what I have so far.  

SELECT COUNT(*)
FROM [Services] s
INNER JOIN .[Clients] c ON c.Client_Id = s.Client_Id
INNER JOIN .[AdHoc_Referrals] r ON r.[Referral_Id] = s.[Referral_Id]
WHERE r.[ReferralTypeId] = @referralTypeId
  AND s.[Service_Delivery_Type_Id] IN ({0})
  AND s.[Inital_Service_Assignment_Date] IS NOT NULL
   OR ( -- This clause is meant to match the first lambda expression
       c.[Firstname] = @searchString
        OR c.[Surname] = @searchString
        OR r.[Reference_Number] = @searchString)
  AND (GETDATE() &gt;= s.Inital_Service_Assignment_Date
       AND @visitDate &lt;= DATEADD(dd, 1, COALESCE(s.Actual_Discharge_Date, s.Inital_Service_Assignment_Date))
      )
Below is a literal translation based on your sample query and as such probably isn't the optimal solution:

IF ISNULL(@firstName, N'') = N'' OR ISNULL(@lastName, N'') = N''
    BEGIN
        SELECT  COUNT(*)
        FROM  [Services] s
            INNER JOIN .[Clients] c
                ON c.Client_Id = s.Client_Id
            INNER JOIN .[AdHoc_Referrals] r
                ON r.[Referral_Id] = s.[Referral_Id]
        WHERE r.[ReferralTypeId] = @referralTypeId
            AND s.[Service_Delivery_Type_Id] IN ({0})
            AND s.[Inital_Service_Assignment_Date] IS NOT NULL
            AND (-- This should be 'AND' to filter properly
                c.[Firstname] = @searchString 
                OR 
                c.[Surname] = @searchString
                OR 
                r.[Reference_Number] = @searchString
                )
            AND (GETDATE() &gt;= s.Inital_Service_Assignment_Date
                    AND @visitDate &lt;= DATEADD(dd, 1,
                            COALESCE(s.Actual_Discharge_Date, 
                                s.Inital_Service_Assignment_Date))
                )  
   END
ELSE
    BEGIN
        SELECT COUNT(*)
        FROM [Services] s
            INNER JOIN .[Clients] c
                ON c.Client_Id = s.Client_Id
            INNER JOIN .[AdHoc_Referrals] r
                ON r.[Referral_Id] = s.[Referral_Id]
        WHERE r.[ReferralTypeId] = @referralTypeId
            AND s.[Service_Delivery_Type_Id] IN ({0})
            AND s.[Inital_Service_Assignment_Date] IS NOT NULL
            AND ( -- Parenthesis not needed, I'm just highlighting the change
                c.[Firstname] LIKE QUOTENAME(RTRIM(@firstName) + N'%')
                AND 
                c.[Surname] = @lastName
                )
            AND (GETDATE() &gt;= s.Inital_Service_Assignment_Date
                    AND @visitDate &lt;= DATEADD(dd, 1,
                            COALESCE(s.Actual_Discharge_Date, 
                                s.Inital_Service_Assignment_Date))
                )  
    END