如果值不为null，则包含在where子句中

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.

我只是根据输入的参数返回值,如果项描述为null,我不想在where子句中包含它。

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'.

但是执行这个查询我得到了一个错误。关键字“CASE”附近的语法不正确。

1 个解决方案

#1

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

您不能在CASE中使用AND部分。试试这个:

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

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

Louaan在评论中提出的另一个选择是这样做:

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.

此选项更好,因为如果变量为null,则不需要sql server来测试实际数据。

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.

注意#1如果其中一列可以为空,则需要决定如何处理空值,因为NULL = NULL总是返回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.

注意#2您可能希望在此存储过程中包含重新编译提示以提高性能。阅读这篇文章,找出原因。

#1