CODEWITHSUNDEEP
sql-servert-sql
Beast-a-tron
Beast-a-tron

Reputation: 523

Inconsistency in T-SQL stored procedure with null comparison checking

I have a stored procedure which contains a null comparison of

--cutting the declaration of various variables
SELECT @val = id FROM dbo.example_table
WHERE type=@type AND date=@date

if @val = null
    insert record

When this is executed in the stored procedure with an empty table, this will insert the record, however when this is executed as a block of code outside the stored proc, it will not perform the insert. There are also other SQL servers on which the insert will never be made, whether inside or outside the stored proc.

Is there some explanation for why his can work in some situations but not in others? I know the correct way to perform the comparison is @val IS NULL, but i was curious to to whether @val = null was documented anywhere for completeness.

Upvotes: 1

Views: 60

Answers (1)

SteveD
SteveD

Reputation: 867

Check the setting for ANSI_NULLS.

This would return a false:

declare @var varchar(10) = null;

SET ANSI_NULLS ON
if (@var = null) select 'true';
else select 'false';

This would return a true:

declare @var varchar(10) = null;

SET ANSI_NULLS OFF
if (@var = null) select 'true';
else select 'false';

Upvotes: 1

Related Questions