How to check isnull or empty in nvarchar column

Transact-SQL
,
1

Hi

How to check for isnull or empty? I'm trying to check as below but its not returning expected results.

AND (CS.StudentName like '%'+ISNULL(@StudentName,CS.StudentName)+'%')
AND (Cl.Street like '%'+ISNULL(@StreetAddress,Cl.Street)+'%')

The above statements are ignoring the rows if the column has explicit NULL value.

Thanks in advance.
--Mohan

2

where (CS.StudentName is null or CS.StudentName = '' )

3

Thanks for your reply Muj9.

But I need to search basing on the input param if user passes either @StreetAddress or @Studentname or both.

4

If you are trying to check for strict equality, do the following:

AND 
	(
		CS.StudentName = @StudentName 
		OR NULLIF(@StudentName,'') IS NULL
	)
AND
	(
		Cl.Street = @StreetAddress 
		OR NULLIF(@StreetAddress,'') IS NULL
	)

If you are trying to do a LIKE condition, then this:

AND 
	(
		CS.StudentName LIKE '%'+@StudentName+'%'
		OR NULLIF(@StudentName,'') IS NULL
	)
AND
	(
		Cl.Street LIKE '%'+@StreetAddress+'%'
		OR NULLIF(@StreetAddress,'') IS NULL
	)

I have to caution you though, that this type of "catch-all" queries tend to perform poorly. Take a look at this blog.

5 
AND (@StudentName IS NULL OR CS.StudentName like '%'+@StudentName+'%')	
AND (@StreetAddress IS NULL OR Cl.Street like '%'+@StreetAddress+'%')