SQL to Include Condition in Where If Not Null

Checking an input param if not Null and using it in where in SQL Server

You can use IsNull

 where some_column = IsNull(@yourvariable, 'valueifnull')

EDIT:

What you described in the comment can be done like:

where (@code is null or code = @code)

SQL - Include where condition if variable is not null

You can use Boolean logic instead of case expression :

where (@schoolId is null) or
      (st.SchoolId = @schoolId);

WHERE Clause only IF NOT NULL

If you want to include records where there's no match, you need an outer join

SELECT beer.id AS beerID,
 beer.barrelID AS barrelID,
 beer.imageID AS imageID,
 beer.title AS title,
 beer.map AS map,
 beer.longitude AS longitude, 
 beer.latitude AS latitude, 
 brand.name AS brandName, 
 brew.type AS brewType,
 image.name AS imageName, 
 variation.name AS variationName 
FROM brand, brew, image, beer
LEFT OUTER JOIN variation ON variation.ID = beer.VariationID
WHERE beer.id = %s 
AND md5(beer.memberID) = %s 
AND beer.review = 1 
AND brand.ID = beer.brandID 
AND brew.ID = beer.brewID 
AND image.ID = beer.imageID 

Include in where clause if the value is not null

You can't use the AND part inside your CASE.
Try this:

WHERE IsActive = @IsActive
AND ItemDescription LIKE CASE WHEN @Description IS NOT NULL THEN '%' + @Description + '%' END
AND InitialPrice >= CASE WHEN @PriceFrom IS NOT NULL THEN @PriceFrom ELSE InitialPrice END
AND InitialPrice <= CASE WHEN @PriceTo IS NOT NULL THEN @PriceTo ELSE InitialPrice END

Another option, as suggested by Louaan in the comments, is to do this:

AND (@Description IS NULL OR ItemDescription LIKE '%'+ @Description +'%')
AND (@PriceFrom IS NULL OR InitialPrice >= @PriceFrom)
AND (@PriceTo IS NULL OR InitialPrice <= @PriceTo)

This option is better since there is no need for the sql server to test the actual data if the variable is null.

Note #1 If either one of the columns is nullable you will need to decide how to treat null values, since NULL = NULL always return false.

Note #2 You might want to include a recompile hint with this stored procedure to improve performance. read this article to find out why.

Conditions if not null in where in SQL Server

you can write the condition in the following way:

AND (table.validityStart IS NULL OR table.validityStart<=@currDate) 
AND (table.validityStop IS NULL OR table.validityStop >=@currDate)

Sql where clause, Get All if value is empty or null else use like statement

Your code can be updated to this:

SELECT [Category]
FROM [dbo].[Records]
WHERE (@SearchText IS NULL OR (Title like '%' + ISNULL( @SearchText ,'')+ '%'))

If you feed null then first condition will be true else second.

---

Related Topics