通用选择不适用于位类型 [英] Generic select doesn't work with bit type

问题描述

基于此答案

我尝试创建一个选择为我的表

i tryed to create a Select for on of my table

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，所以我错过了什么？

but i got a problem with the bit type example sqlfiddle like you can see it should return 4 rows but it only returns 3 so what do i miss?

推荐答案

这是因为你可以有 null 作为你的列的值。而SQL有三值逻辑，所以检查 null = null 将返回 UNKNOWN 而不是 TRUE （如你所期望的那样）。
我认为这个查询会帮助你：

It's because you can have null as values of your columns. And SQL have three-value logic, so checking null = null will return UNKNOWN instead of TRUE (as you may expect). I think this query will help you:

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演示

这篇关于通用选择不适用于位类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！