CODEWITHSUNDEEP
sqlnullcasewhere-clause
user1545878
user1545878

Reputation: 35

SQL Server: WHERE clause with null comparison

I have SP that has a parameter @NumeroTransferencia int. I need to select rows that match that variable when it is not null. But when it's null, I have to return the ones which have H.CodigoTransferencia = null I tried the following but I didn't get a good result: 

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

Thanks!

Upvotes: 1

Views: 68

Answers (4)

Kiran Hegde
Kiran Hegde

Reputation: 3681

You can try this

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

Upvotes: 1

Ndech
Ndech

Reputation: 965

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

Upvotes: 1

Screech129
Screech129

Reputation: 110

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

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

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

Upvotes: 0

Related Questions