How can I use IF ELSE in WHERE clause on the basis of a string variable?

I have an SP. It returns some number of rows. Here is the SP:

ALTER PROCEDURE [dbo].[spGetClients]

( @orderBy varchar(50), @startAge int, @endAge int, @sex char, @staffId nvarchar(1024), @statusId nvarchar(1024), @ethnicityId nvarchar(1024), @treatmentProviderId nvarchar(1024) ) AS BEGIN SET NOCOUNT ON;

IF(@orderBy = 'Consumer Name')
BEGIN
    SELECT c.Id, dbo.GetClientFullName(c.FirstName, c.MiddleInit, c.LastName) AS ClientName, 
    c.DateOfBirth, dbo.GetAge(c.DateOfBirth, GETDATE()) AS Age, c.Sex, 
    dbo.GetClientStatus(c.Id, @statusId) AS Status, ca.Address, co.Phone, 
    dbo.GetEthnicity(c.Id, @ethnicityId) AS Ethnicity, dbo.GetDevelopmentalDisabilities(c.Id) AS Disabilities, 
    dbo.GetClientStaffContacts(c.Id, @staffId) AS Staff, dbo.GetClientContacts(c.Id) AS Contact, 
    dbo.GetClientInsuranceProviders(c.Id) AS HealthProvider, 
    dbo.GetClientTreatmentProviders(c.Id, @treatmentProviderId) AS TreatmentProvider
    FROM Client c  
    LEFT OUTER JOIN(        
        SELECT ca.ParentEntityId, ca.Address
        FROM ContactAddress ca
        INNER JOIN EntityName en ON en.Id = ca.EntityNameId AND en.Name = 'Client' 
        INNER JOIN GeneralLookup gl ON ca.glAddressTypeId = gl.Id AND gl.LookupItem = 'Primary'    
    ) ca ON c.Id = ca.ParentEntityId 
    LEFT OUTER JOIN(        
        SELECT co.ParentEntityId, co.ContactData Phone
        FROM ContactOther co
        INNER JOIN EntityName en ON en.Id = co.EntityNameId AND en.Name = 'Client' 
        INNER JOIN GeneralLookup gl ON co.glContactTypeId = gl.Id AND gl.LookupItem = 'Home'    
    ) co ON c.Id = co.ParentEntityId 
    LEFT OUTER JOIN GeneralStatus gs on gs.Id = c.StatusId
    where gs.Name <> 'Deleted' 
    and (dbo.GetAge(c.DateOfBirth, GETDATE()) BETWEEN @startAge and @endAge)
    and ((@sex = 'M' and c.sex = 'M') or (@sex = 'F' and c.Sex = 'F') or (@sex = '' and (c.Sex in ('M', 'F', ''))))
    and dbo.GetClientStaffContacts(c.Id, @staffId) is not null
    and dbo.GetClientStaffContacts(c.Id, @staffId) is not null
    and dbo.GetClientStatus(c.Id, @statusId) is not null 
    and dbo.GetEthnicity(c.Id, @ethnicityId) is not null 
    and dbo.GetClientTreatmentProviders(c.Id, @treatmentProviderId) is not null
    ORDER BY ClientName
END
END

The UDFs(User defined functions) are dbo.GetClientStaffContacts(c.Id, @staffId) and other. They return some rows which are having null as well. That's why I'm using last 5 statements in where clause so that only non null values can be collected. Everything is working fine but the problem is, I want this check of non null only 开发者_JS百科when these variables are NOT empty string:

@staffId nvarchar(1024), 
@statusId nvarchar(1024), 
@ethnicityId nvarchar(1024), 
@treatmentProviderId nvarchar(1024)

How can I resolve this issue? I want something like this:

If @staffId <> '' then dbo.GetClientStaffContacts(c.Id, @staffId) is not null

---

 AND  ((@staffId = "") OR (dbo.GetClientStaffContacts(c.Id, @staffId) is not null))

---

You can't do logical comparisons within a CASE statement. You have to produce a value from a case expression, and the type of each possible value produced must be the same, or convertible:

CASE @staffId
    WHEN "" THEN 1
    ELSE
       CASE
           WHEN dbo.GetClientStaffContacts(c.Id, @staffId) is not null THEN 1
           ELSE 0
       END
END = 1

Would be a condition you could put in your WHERE clause

---

There are two forms of CASE expression possible:

• Simple CASE (when you're trying to find a WHEN clause that is equal to the provided value after the keyword CASE) - this is what the outer CASE expression is doing (trying to find a WHEN clause that matches the @StaffId value).
• Searched CASE (when you're trying to match a WHEN clause based on arbitrary predicates) - this is what the inner CASE expression is doing (testing whether the return of the function is not null)

You can't determine NULL/non-NULL status using a Simple CASE expression, since NULL is not equal to NULL (nor is it not equal).