MSSQL variable = null

Question

Anyone know why a situation like the following would run fine on MSSQL 2005 and not MSSQL 2008:

declare @X int = null;

select A, B, C from TABLE where X=@X

Without going into detail, I've got a stored proc which calls another stored proc that takes a hard coded Null as one of the parameters and it runs fine apparently on MSSQL2005 but not 2008.

That will NOT work as coded in sql 2005. You can't assign a default value to a variable until 2008. And in ALL versions of sql server that will never work because an expression will never evaluate to true when either side is NULL. — Sean Lange, Jan 19 '16 at 16:29
This seems like answered before. http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server — Engin, Jan 19 '16 at 16:32
@SeanLange: SQL Server returns `TRUE` for `NULL=NULL` when you `SET ANSI_NULLS ON` :-) — dnoeth, Jan 19 '16 at 16:38
@dnoeth that is true...but just shoot me if I ever have to work with a system like that. IIRC the ability to set that to OFF is going away at some point in the future. — Sean Lange, Jan 19 '16 at 16:39

score 1 · Answer 1 · answered Jan 19 '16 at 16:37

1

The code is poorly written regardless of which version of SQL you're using, because NULL is never "equal" to anything (even itself). It's "unknown", so whether or not it's equal (or greater than, or less than, etc.) another value is also "unknown".

One thing that can affect this behavior is the setting of ANSI_NULLS. If your 2005 server (or that connection at least) has ANSI_NULLS set to "OFF" then you'll see the behavior that you have. For a stored procedure the setting is dependent at the time that the stored procedure was created. Try recreating the stored procedure with the following before it:

SET ANSI_NULLS ON
GO

and you'll likely see the same results as in 2008.

You should correct the code to properly handle NULL values using something like:

WHERE X = @X OR (X IS NULL AND @X IS NULL)

or

WHERE X = COALESCE(@X, X)

The specifics will depend on your business requirements.

answered Jan 19 '16 at 16:37

Tom H

46,766
14
87
128

Of course Standard SQL treats NULLs equal in some cases, e.g. `DISTINCT` or `GROUP BY`. One of the reasons why nobody likes NULLs :-) – dnoeth Jan 19 '16 at 16:42
Technically, the ANSI standard calls for NULL values to be ignored in aggregate functions (with a few exceptions), so they aren't considered equal, they just aren't taken into account. So, if I have two out of three columns that match in two rows and the third column is NULL in both then when I compare if I ignore the NULL values then the rows are grouped together. – Tom H Jan 19 '16 at 18:01
I meant NULLs in a GROUP BY column. – dnoeth Jan 19 '16 at 18:09
Yes, if you GROUP BY a column that contains NULL then SQL will combine all rows that have NULL in that column as one row in the results, but it's not because SQL is saying that they're equal it's because for that one group it's ignoring that column completely. Mostly a distinction without a difference though. – Tom H Jan 19 '16 at 18:19

score 1 · Accepted Answer · answered Jan 19 '16 at 16:37

That might be due to your ansi_null settings in two servers.

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

You can find detailed information here: https://msdn.microsoft.com/en-us/library/ms188048(v=sql.90).aspx

I'll check and see if that is it. So far, it seems the most likely. Thanks for your prompt reply! — Das, Jan 19 '16 at 17:10
Avoid setting ANSI_NULLS OFF, Microsoft said to not do that on the link Engin provided to you — DaFi4, Jul 12 '16 at 13:44

score 0 · Answer 3 · answered Jan 19 '16 at 16:27

0

Try this:

select A, B, C from TABLE where X IS NULL

The reason why your original query did not return the expected result is explained here

answered Jan 19 '16 at 16:27

Fuzzy

3,810
2
15
33

score -1 · Answer 4 · answered Jan 19 '16 at 18:06

-1

SET ANSI_NULLS OFF GO

^^That made the stored procs work the way I expected.

answered Jan 19 '16 at 18:06

Das

1
1

you should mark the answer as accepted, and not post this as a solution. This is also the worst possible solution, normally, because from Microsoft: "Important In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature." https://msdn.microsoft.com/en-us/library/ms188048(v=sql.110).aspx – DaFi4 Jul 12 '16 at 13:40

MSSQL variable = null

4 Answers4