通用选择不适用于位类型

一尘不染

通用选择不适用于位类型

sql

我试图在我的桌子上创建一个Select for

ALTER PROCEDURE _Einrichtung_Select

    -- Parameters with default values
        @EinrichtungId      AS int          = NULL,
        @EinrichtungName    AS nvarchar(50) = NULL,
        @IsKueche           AS bit          = NULL,
        @RefEinrichtungId   AS int          = NULL,
        @RefSpeiseplantypId AS int          = NULL

AS

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- generic SELECT query
        SELECT  *
        FROM    Einrichtung
        WHERE   EinrichtungId       = ISNULL(@EinrichtungId,        EinrichtungId)
        AND     EinrichtungName     = ISNULL(@EinrichtungName,      EinrichtungName)
        AND     IsKueche            = ISNULL(@IsKueche,             IsKueche)
        AND     RefEinrichtungId    = ISNULL(@RefEinrichtungId,     RefEinrichtungId)
        AND     RefSpeiseplantypId  = ISNULL(@RefSpeiseplantypId,   RefSpeiseplantypId)

        ORDER BY EinrichtungName

    RETURN

但是我对位类型示例sqlfiddle遇到了问题，如您所见，它应该返回4行，但它仅返回3行，所以我想念什么？

阅读 138

2021-03-08

共1个答案

一尘不染

这是因为您可以将nullas作为列的值。而且SQL具有三值逻辑，因此检查null = null将返回，UNKNOWN而不是TRUE（如您期望的那样）。我认为此查询将为您提供帮助：

select *
from myTable
where
    (@EinrichtungId is null or EinrichtungId = @EinrichtungId) and
    (@EinrichtungName is null or EinrichtungName = @EinrichtungName) and
    (@IsKueche is null or IsKueche = @IsKueche) and
    (@RefEinrichtungId is null or RefEinrichtungId = @RefEinrichtungId) and
    (@RefSpeiseplantypId is null or RefSpeiseplantypId = @RefSpeiseplantypId)

[sql fiddle demo](http://sqlfiddle.com/#!3/0cedb/25)

2021-03-08