SQL SERVER 下的NULL( is null 和 =null) 与 case when

CREATE TABLE AAAA
(
  id int,
  salary numeric(16,2)
)
GO
INSERT INTO AAAA(ID) VALUES (1)
INSERT INTO AAAA(ID) VALUES (2)
INSERT INTO AAAA(ID) VALUES (3)
INSERT INTO AAAA(ID) VALUES (4)
INSERT INTO AAAA(ID) VALUES (5)
INSERT INTO AAAA(ID) VALUES (6)
INSERT INTO AAAA(ID) VALUES (7)
INSERT INTO AAAA VALUES (8,330.00)

下面是测试case:

 

Case 1:

SET ANSI_NULLS ON -- 那么NULL=NULL
SELECT 
CASE WHEN salary=NULL THEN '''' ELSE salary END AS s
FROM AAAA
ORDER BY salary DESC;

/*

Success

 

*/

CASE 2:

SET ANSI_NULLS OFF --那么NULL!=NULL
SELECT 
CASE WHEN salary=NULL THEN '''' ELSE salary END AS s
FROM AAAA
ORDER BY salary DESC;

/*

Fail

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

*/

 

Case 3:

SELECT 
CASE WHEN salary is null THEN '''' ELSE salary END AS s
FROM AAAA
ORDER BY salary DESC;

/*

Fail

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

 

*/

 

Case 4: = Case 1

SELECT 
CASE salary WHEN null THEN '''' ELSE salary END AS s
FROM AAAA
ORDER BY salary DESC;

/*

When SET ANSI_NULLS ON

Success

When SET ANSI_NULLS OFF

Fail

*/

 

Case 5:

SELECT 
CASE WHEN salary is null THEN '' ELSE ltrim(salary) END AS s
FROM AAAA
ORDER BY salary DESC

/*

Success

*/

 

结论：

 

case when 下的每个可能性输出必须具有同样的data type.