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.
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
)
)
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,'')
How about ISNULL?
Replaces NULL with the specified replacement value.
Something like
select *
from [dbo].[ViewsConfiguration]
where PolicyId= 139
and EventName= ISNULL(@test,EventName)
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))
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
ISNULL/ COALESCElike thisand ISNULL(EventName,'')= ISNULL(@test,'')