SQL Server:具有null比较的WHERE子句

Question

我有一个SP，它有一个参数@NumeroTransferencia int。当变量不为null时，我需要选择与该变量匹配的行。但是当它为null时，我必须返回那些H.CodigoTransferencia为null的我尝试了以下操作，但没有得到好的结果：

SELECT 
*
FROM RUTEO.HOJADERUTA H
WHERE 
    (H.CodigoTransferencia IS NULL OR H.CodigoTransferencia = @NumeroTransferencia)

谢谢！

Answer 1

SELECT 
*
FROM RUTEO.HOJADERUTA H
WHERE 
    ((H.CodigoTransferencia IS NULL AND @NumeroTransferencia IS NULL) OR H.CodigoTransferencia = @NumeroTransferencia)

Answer 2

你可以试试这个

SELECT 
*
FROM RUTEO.HOJADERUTA H
WHERE 
    (@NumeroTransferencia IS NULL 
       AND H.CodigoTransferencia IS NULL)
 OR (@NumeroTransferencia IS NOT NULL 
      AND H.CodigoTransferencia = @NumeroTransferencia)

Answer 3

SELECT *
FROM   RUTEO.HOJADERUTA H
WHERE  (
           @NumeroTransferencia IS NULL 
           AND H.CodigoTransferencia IS NULL
       )
       OR 
       (
           @NumeroTransferencia IS NOT NULL 
           AND H.CodigoTransferencia = @NumeroTransferencia
       )

Answer 4

SELECT 
*
FROM RUTEO.HOJADERUTA H
WHERE 
    ((@NumeroTransferencia IS NULL OR H.CodigoTransferencia = @NumeroTransferencia) H.CodigoTransferencia IS NULL) 
This will only perform the search if your variable is not null otherwise it will look for H.CodigoTransferencia is equal to your variable otherwise where H.CodigoTransferencia = null