Checking for Null values in sp

[AGus01]

Hi i have this query which is checking for duplicate entries in the database, based on criteria Surname and (mobile telephone number and/or date of birth ) My problem is that date of birth and mobile telephone number are non-mandatory so could be either be null of zero length string returned .

I will need some sort of CASE statement in My where clause which if @srtDateofBirth is /zero length stringNull/zero length string or CT.TelephoneNumber is Null it returns false.

Please can someone help me.

Many thanks

gus

select 
	@intCandCount = count(CPD.CandID) 
from 
	CandPersonaldetails CPD
	join CandApplication CA on CA.CandID = CPD.CandID
	left outer join CandTelephone CT on CA.Candid  = CT.Candid and CT.TelephoneTypeId = 4 
where
	CPD.Surname = @strSurname 
and 
	(
	CPD.DateOfBirth = @strDateofBirth	
	
	or
		(	
		Left(CT.TelephoneNumber, 9) = Left(@TelephoneNumber, 9) 
		)
	)
and
    CPD.ActiveStatusID = 1 
and 
	CA.ApplicationStatusID = 3
and 
	CA.StageID > 2	
group by 
	CPD.Surname

 

[djj55]

Have you tried something like:
SUM(CASE WHEN phone IS NULL THEN 0 ELSE (CASE DOB IS NULL THEN 0 ELSE 1 END) END)
djj