在 sql 中以空值形式返回整个数据

Question

我这里有一个情况，如果我的参数为 null，我想从表中选择所有数据，否则它应该根据条件工作，

SELECT 
    CA.ContactAddressId,
    CA.CompanyName AS CompanyName,
    CA.ContactPerson AS ContactPerson,
    CA.Address1 AS Street,
    ISNULL(CA.Address2,'') AS Street2,
    CA.City,
    CA.[State] AS [State],
    CA.Country AS CountryCode,
    CASE
        WHEN CA.Country = 1 THEN 'USA'
        WHEN CA.Country = 2 THEN 'CANADA'
        WHEN CA.Country = 4 THEN 'MEXICO'
    END AS Country,
    CA.ZipCode,
    CA.WorkPhone AS Phone,
    CA.WorkPhoneExt AS Ext,
    ISNULL(CA.Fax,'') AS Fax,
    CA.Email AS [Email],
    CA.AddressType,
    CA.AddressCode,
    CADS.PickupReadyTime AS PickupReadyTime,
    CADS.PickupCloseTime AS PickupCloseTime,
    CADS.DeliveryReadyTime AS DeliveryOpeningTime,
    CADS.DeliveryCloseTime AS DeliveryClosingTime,
    CAST(ISNULL(CAD.ContactAddressId,0) as bit) IsDefault
INTO #ContactAddressDetails
FROM CR2.ContactAddress AS CA WITH (NOLOCK)
INNER JOIN #ContactGroupList list
    ON list.ContactGroupId = CA.ContactGroupId
LEFT JOIN CR2.ContactAddressDefaultSettings AS CADS WITH (NOLOCK)
    ON CADS.ContactAddressId = CA.ContactAddressId
LEFT JOIN CR2.ContactAddressDefault CAD WITH (NOLOCK) 
    ON CAD.ContactAddressId = CA.ContactAddressId 
    AND CAD.UserId = @LoggedInUserId 
    --and CAD.DefaultAddressType = @DefaultAddressType
 WHERE (@ZipCodeOrigin IS Null Or CA.ZipCode IN (@ZipCodeOrigin,@ZipCodeDestination)) 
   AND (@ZipCodeDestination Is Null OR CA.ZipCode IN(@ZipCodeOrigin,@ZipCodeDestination))

这里@ZipCodeOrigin和@ZipCodeDestination是参数，如果它们为 null，我必须从表中获取所有数据。

希望我的问题陈述清楚。

Answer 1

如果两个值都不为 null，则条件使用相同的条件筛选数据两次。我会这样写：

 WHERE (@ZipCodeOrigin IS NULL AND @ZipCodeDestination IS Null)  OR CA.ZipCode IN (@ZipCodeOrigin, @ZipCodeDestination)) 

Answer 2

如果可以选择

使用简单串联以编程方式生成查询，则当条件为 null 时，只需不连接 ZipCodeOrigin 上的条件。

如果您必须坚持使用准备好的语句，解决方案是再创建 3 个准备好的语句，一个在 ZipCodeOrigin 上没有条件，另一个在 ZipCodeDestination 上没有条件，另一个完全没有这两个条件（并且没有 WHERE 子句）。

啊另一个令人讨厌的解决方案是使用 LIKE，如果 ZipCodeOrigin 为空，则使其为"%"以匹配所有内容。

Answer 3

将您的条件更改为：

   WHERE CA.ZipCode  = ( Case When @ZipCodeOrigin IS Null Then CA.ZipCode Else                       @ZipCodeOrigin END)
      Or  CA.ZipCode = ( Case When @ZipCodeDestination IS Null Then CA.ZipCode Else @ZipCodeDestination END)