After changing the ANSI null settings in stored procedures and functions so that they all corresponded, I found some code that was depending on the SET ANSI_NULLS OFF setting to interpret the results using a case statement. The case statement was comparing NULL and returning 0 or 1 as the result.
The offending code was similar to:
CASE sub1.[EmpID]
WHEN NULL THEN 0
ELSE 1
END AS Selected
This only works when SET ANSI_NULLS OFF is specified.
This can be fixed to work correctly no matter the ANSI_NULLS setting ( ON | OFF ) by changing the case statement to the following:
CASE
WHEN sub1.[EmpID] IS NULL THEN 0
ELSE 1
END AS Selected
WHEN sub1.[EmpID] IS NULL THEN 0
ELSE 1
END AS Selected
.
Here is a quick example showing the issue:
/* ANSI NULL example with case statement */
/* Setup some tables in the test */
CREATE SCHEMA test ;
GO
CREATE TABLE test.emp
(
empID INT IDENTITY(1, 1)
,FirstName NVARCHAR(32)
,LastName NVARCHAR(32)
,CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED ( [empID] ASC ) )
ON [PRIMARY]
GO
CREATE TABLE test.EmpGroup
(
EmpGroupID INT IDENTITY(1, 1)
,EmpGroupName NVARCHAR(32)
,CONSTRAINT [PK_EmpGroup] PRIMARY KEY CLUSTERED ( [EmpGroupID] ASC ) )
ON [PRIMARY]
GO
CREATE TABLE test.EmpGroupLink
(
EmpID INT
,EmpGroup INT
,CONSTRAINT [PK_EmpGroupLink] PRIMARY KEY CLUSTERED ( EmpID, EmpGroup ASC ) )
ON [PRIMARY]
GO
ALTER TABLE test.EmpGroupLink ADD CONSTRAINT FK_EmpGroupLink_Emp FOREIGN KEY (EmpID)
REFERENCES test.emp (empID)
ALTER TABLE test.EmpGroupLink ADD CONSTRAINT FK_EmpGroupLink_EmpGroup FOREIGN KEY (EmpGroup)
REFERENCES test.EmpGroup(EmpGroupID)
GO
/* add some records for our testing */
INSERT INTO test.emp
( FirstName, LastName )
VALUES ( 'Samir', 'Nagheenanajar' ),
( 'Mike', 'Bolton' ),
( 'Peter', 'Gibbons' ),
( 'Bill', 'Lumbergh' ),
( 'Milton', 'Waddams' ) ;
GO
INSERT INTO test.EmpGroup
( EmpGroupName )
VALUES ( 'Development' ),
( 'Management' ),
( 'Operations' )
GO
INSERT INTO test.EmpGroupLink
( EmpID, EmpGroup )
VALUES ( 1, 1 ),
( 2, 1 ),
( 3, 1 ),
( 4, 2 ),
( 5, 3 );
GO
SET ANSI_NULLS OFF
go
SELECT E.empID
,E.FirstName
,E.LastName
,CASE sub1.[EmpID]
WHEN NULL THEN 0
ELSE 1
END AS Selected
FROM test.emp E
LEFT JOIN ( SELECT EmpID
FROM test.EmpGroupLink EGL
JOIN test.EmpGroup EG ON EGL.EmpGroup = EG.EmpGroupID
WHERE EG.EmpGroupName = 'Management' ) sub1 ON E.empID = sub1.EmpID
GO
/* If ANSI_NULLS ON returns unintended results*/
SET ANSI_NULLS ON
GO
SELECT E.empID
,E.FirstName
,E.LastName
,CASE sub1.[EmpID]
WHEN NULL THEN 0
ELSE 1
END AS Selected
FROM test.emp E
LEFT JOIN ( SELECT EmpID
FROM test.EmpGroupLink EGL
JOIN test.EmpGroup EG ON EGL.EmpGroup = EG.EmpGroupID
WHERE EG.EmpGroupName = 'Management' ) sub1 ON E.empID = sub1.EmpID
/* changed to handle NULLS correctly no matter the ANSI settings */
SET ANSI_NULLS OFF
GO
SELECT E.empID
,E.FirstName
,E.LastName
,CASE WHEN sub1.[EmpID] IS NULL THEN 0
ELSE 1
END AS Selected
FROM test.emp E
LEFT JOIN ( SELECT EmpID
FROM test.EmpGroupLink EGL
JOIN test.EmpGroup EG ON EGL.EmpGroup = EG.EmpGroupID
WHERE EG.EmpGroupName = 'Management' ) sub1 ON E.empID = sub1.EmpID
GO
SET ANSI_NULLS ON
GO
SELECT E.empID
,E.FirstName
,E.LastName
,CASE WHEN sub1.[EmpID] IS NULL THEN 0
ELSE 1
END AS Selected
FROM test.emp E
LEFT JOIN ( SELECT EmpID
FROM test.EmpGroupLink EGL
JOIN test.EmpGroup EG ON EGL.EmpGroup = EG.EmpGroupID
WHERE EG.EmpGroupName = 'Management' ) sub1 ON E.empID = sub1.EmpID
GO
As we can see developers can sometimes use null comparisons incorrectly.
