SQL Check for null values in variables

Question

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.

Answer 1

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
                 )
            )

Answer 2

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,'')