SQL Check Values ​​in Variables

Question

I am trying to compare the value of a table with a variable, which may be null , however, since we cannot equate this to zero, 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 it.

Answer 1

You can use ISNULL / COALESCE . Your request will look like

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

Answer 2

You can compare both with NULL :

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

Answer 3

How about ISNULL ?

Replaces the NULL value with the specified replacement value.

Sort of

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

Answer 4

You can explicitly check it with the is statement:

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

Answer 5

If you need null strings, 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)) supplies null if the values ​​are either null or equal:

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