How to check a field with SQL variable, which can be null?

Question

I have the following SQL:

CREATE TABLE tbFoo(
    a varchar(50) NULL,
) 


CREATE NONCLUSTERED INDEX IX_tbFoo_a ON tbFoo
(
    a ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

insert into tbFoo select null
insert into tbFoo select 'test'

The following two queries work fine and use my index as expected:

select * from tbFoo where a='test'
select * from tbFoo where a is null

Now, let's say I want to store my comparison value in a variable, for example:

declare @a varchar(50)
select @a = NULL

The following query will not return the expected results if @a is null, because I have to use the "is" operator, not the "="

select * from tbFoo where a=@a 

The following will work, but will scan the table if @a is null (because of the string "test", which forces the second bracket to be evaluated)

select * from tbFoo where (a is null and @a is null) or (a=@a)

In the end, I came up with this solution that works fine and uses my index:

select * from tbFoo where (a is null and @a is null) or (@a is not null and a=@a)

Am I analyzing the situation correctly?

Is there a better way to handle this situation?

Answer 1

, , :

SQL Server 2008 , NULL:

CREATE UNIQUE NONCLUSTERED INDEX IX_tbFoo_a 
ON tbFoo (a)
WHERE a IS NOT NULL;

Answer 2

"" NULL... NULL.

. , . , a NULL .

Answer 3

ansi nulls off off

set ansi_nulls off

declare @a varchar(50)
select @a = NULL

select * from tbFoo where a=@a

set ansi_nulls on

,

Answer 4

, , , , :

if @a IS NULL
    select * from tbFoo where a is null
else
    select * from tbFoo where a = @a

, if @a IS NULL , . , , .

Answer 5

, . . , @a sproc. "somethingweird" , "~~~" - .

set @a = isnull(@a,'somethingweird')
select * from tbFoo where isnull(a,'somethingweird')=@a

Answer 6

, , , . UNION OR...

select * from tbFoo where (a is null and @a is null)
UNION ALL
select * from tbFoo where (a=@a and @a is not null)

( , "@a " , . , , . , .)

, UNION :
- . -

- :)

Answer 7

Just isnull both sides, like ...

DECLARE @random VARCHAR(50)
SELECT  @random = 'text that never appears in your table'

SELECT * FROM @tbFoo WHERE ISNULL(a, @random) = ISNULL(@a, @random)

Answer 8

Your analysis is correct - and why 3-digit logic makes life difficult.

The offer from @StriplingWarrior is good; it solves the problem by executing various SQL depending on whether the variable is null or not. If this is not possible, you will need your long solution that reuses the host variable.