CODEWITHSUNDEEP
sql-servernull
SJMan
SJMan

Reputation: 1607

SQL Check for null values in variables

I am trying to compare a table value to a variable which can be null, however since we cannot equate this to null hence this is not comparable.

declare @test varchar(33) -- This can or cannot be NULL;
select * from [dbo].[ViewsConfiguration] where PolicyId= 139 and EventName= @test

This can be done using switch or if , however looking for a more elegant way to do this.

Upvotes: 5

Views: 645

Answers (5)

flo
flo

Reputation: 10241

If you only want null rows if @test is null you can use a combination of NULLIF and ISNULL:

SELECT * FROM [dbo].[ViewsConfiguration] WHERE PolicyId= 139 
     AND ISNULL(NULLIF(EventName, @test), NULLIF(@test, EventName)) IS NULL

ISNULL(NULLIF(EventName, @test), NULLIF(@test, EventName)) delivers null if the values are both null or if they are equal: 

EventName      @test        Result
----------------------------------
null           null         null   
[value]        null         [value]   
null           [value]      [value]  
[value1]       [value2]     [value1]  
[value]        [value]      null

Upvotes: 0

ughai
ughai

Reputation: 9880

You can use ISNULL / COALESCE. Your query would be like

declare @test varchar(33) -- This can or cannot be NULL;
select * from [dbo].[ViewsConfiguration] where PolicyId= 139 and ISNULL(EventName,'')= ISNULL(@test,'')

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166326

How about ISNULL?

Replaces NULL with the specified replacement value.

Something like

select * 
from [dbo].[ViewsConfiguration] 
where PolicyId= 139 
and EventName= ISNULL(@test,EventName)

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can compare both to NULL:

DECLARE @test VARCHAR(33) 
SELECT  *
FROM    [dbo].[ViewsConfiguration]
WHERE   PolicyId = 139
        AND ( EventName = @testd
              OR ( EventName IS NULL
                   AND @testd IS NULL
                 )
            )

Upvotes: 2

Mureinik
Mureinik

Reputation: 310993

You could check for it explicitly with the is operator:

SELECT * 
FROM   [dbo].[ViewsConfiguration] 
WHERE  PolicyId = 139 AND 
       (EventName = @test OR (@test IS NULL AND EventName IS NULL))

Upvotes: 0

Related Questions