Include in where clause if the value is not null

Question

I just return values based on inputted parameters, if the item description is null, I don't want to include that in the where clause.

Like item description, all other cases were same.

ALTER Procedure [dbo].[WP_GetItems] 
    @IsActive     bit,
    @OrderMode    bit,
    @OrderBy      varchar(75),
    @Description  varchar(250),
    @DateFrom     datetime,
    @DateTo       datetime,
    @PriceFrom    float,
    @PriceTo      float
as
Begin 
    Select ItemID, ItemPartNumber, ItemDescription, CreatedDate, InitialPrice from Items where IsActive = @IsActive
    CASE
        WHEN @Description IS NOT NULL THEN AND ItemDescription LIKE '%' + @Description + '%' 
        WHEN @PriceFrom IS NOT NULL THEN AND InitialPrice >= @Price
        WHEN @PriceTo IS NOT NULL THEN AND InitialPrice <= @Price
    END 
    order by
    CASE WHEN @OrderBy='ItemDescription' AND @OrderMode = 0 THEN ItemDescription END ASC,
    CASE WHEN @OrderBy='ItemDescription' AND @OrderMode = 1 THEN ItemDescription END DESC,
    CASE WHEN @OrderBy='ItemPartNumber' AND @OrderMode = 0 THEN ItemPartNumber END ASC,
    CASE WHEN @OrderBy='ItemPartNumber' AND @OrderMode = 1 THEN ItemPartNumber END DESC,
    CASE WHEN @OrderBy='CreatedDate' AND @OrderMode = 0 THEN CreatedDate END ASC,
    CASE WHEN @OrderBy='CreatedDate' AND @OrderMode = 1 THEN CreatedDate END DESC,
    CASE WHEN @OrderBy='InitialPrice' AND @OrderMode = 0 THEN InitialPrice END ASC,
    CASE WHEN @OrderBy='InitialPrice' AND @OrderMode = 1 THEN InitialPrice END DESC
End

But executing this query I got an error. Incorrect syntax near the keyword 'CASE'.

Answer 1

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.