/* Case 4: @VAR = @VAR */
declare
@i int
= null, @j int
= null;
set ansi_nulls off
;
if
@i = @j
select
'True'
as
[Case 4, ansi_nulls OFF]
else
select
'FALSE'
as
[Case 4, ansi_nulls OFF];
set ansi_nulls on
;
if
@i = @j
select
'True'
as
[Case 4, ansi_nulls ON]
else
select
'FALSE'
as
[Case 4, ansi_nulls ON];
if
(@i = @j)
or (@i is null and @j is null)
select
'True'
as
[Case 4, ansi_nulls ON, solution 1]
else
select
'FALSE'
as
[Case 4, ansi_nulls ON, solution 1];
if
exists(select @i intersect select
@j)
select
'True'
as
[Case 4, ansi_nulls ON, solution 2]
else
select
'FALSE'
as
[Case 4, ansi_nulls ON, solution 2];
go
/* Case 5: @VAR = COLUMN */
create table
T (x int
not null, j int
null);
insert into
T values
(1, null);
declare
@i int
= null;
set ansi_nulls off
;
if
@i = (select
j from T where
x = 1)
select
'True'
as
[Case 5, ansi_nulls OFF]
else
select
'FALSE'
as
[Case 5, ansi_nulls OFF];
set ansi_nulls on
;
if
@i = (select
j from
T where
x = 1)
select
'True'
as
[Case 5, ansi_nulls ON]
else
select
'FALSE'
as
[Case 5, ansi_nulls ON];
/* Solution that can cause bad performance */
if
(@i = (select
j from
T where
x = 1))
or (@i is null and (select
j from
T where
x = 1) is null)
select
'True'
as
[Case 5, ansi_nulls ON, solution 1]
else
select
'FALSE'
as
[Case 5, ansi_nulls ON, solution 1];
/* Preferred solution */
if
exists (select
@i intersect
(select
j from
T where
x = 1))
select
'True'
as
[Case 5, ansi_nulls ON, solution 2]
else
select
'FALSE'
as
[Case 5, ansi_nulls ON, solution 2];
go
drop Table
T;
go
/* Case 6: COLUMN IN (SET OF LITERALS) */
create table
T (x int
not null, j varchar
(15) null);
insert into
T values
(1, null), (2, ''
), (3, 'Hello world'
);
go
set ansi_nulls off
;
select
[case] = 'Case 6, ansi_nulls OFF'
, * from
T where
j in (''
, null);
set ansi_nulls on
;
select
[case] = 'Case 6, ansi_nulls ON'
, * from
T where
j in (''
, null);
/* Solution that can cause bad performance */
select
[case] = 'Case 6, ansi_nulls ON, solution 1'
, * from
T where
j = ''
or j is null;
/* Solution can be used if column j is not indexed */
select
[case] = 'Case 6, ansi_nulls ON, solution 2'
, * from
T where
ISNULL(j, ''
) = ''
;
/* Preferred solution */
select
[case] = 'Case 6, ansi_nulls ON, solution 3',
* from
T where
j = ''
union
select
[case] = 'Case 6, ansi_nulls ON, solution 3'
, * from
T where
j is null
drop Table
T;
go
/* Case 7: @VAR IN (SELECT COLUMN) */
create tabl
e T (i int
null)
go
insert into
T values
(1), (2), (null), (3)
go
declare
@A int
= null;
set ansi_nulls off
if
@A in (select
i from
T) print
'case 7, ANSI_NULLS = OFF: true'
else print
'case 7, ANSI_NULLS = OFF: FALSE'
set ansi_nulls on
if
@A in (select
i from
T) print
'case 7, ANSI_NULLS = ON: true'
else print
'case 7, ANSI_NULLS = ON: FALSE'
if
exists (select
@A intersect
(select
i from
T)) print
'case 7, Solve: true'
else print
'case 7, Solve: FALSE'
drop table
T
go