T-SQL中的一些小陷阱

1，当心ISNULL函数对你的逻辑引起BUG

 

　　　　有人喜欢或者习惯于(并不代表我推荐，甚至这种写法没有任何好处)用ISNULL处理变量这种方式写查询

　　　　比如：select * from TestISNULL where name = ISNULL(@name,name)

　　　　@name相当于传入到存储过程中的参数，如果@name为null，

　　　　相当于：select * from TestISNULL where name = name，看起来恒成立，是真的吗？

　　　　目的是在@name为null的时候，这个查询条件不生效

　　　　当时当遇到字段name中的某些数据为null的时候，实际逻辑就变成了

　　　　select * from TestISNULL where null= null，null=null是什么结果？

　　　　自己试试或者继续往下看就知道了，

　　　　此时会产生一些“莫名其妙”的bug，这种bug是跟数据有关，有时候出现，而有时候又没有问题，

　　　　对于“有时候出现有时候不出现的问题”，不管是SQL还是应用程序代码，定位起来都不是太容易的

　　　　这种情况，会对对问题的排查产生了很大的干扰

　　　　看例子：

--ISNULL 处理变量的一个小陷阱
Create Table TestISNULL
(
    id int,
    name varchar(50)
)
GO

insert into TestISNULL values (1,'AAA')
insert into TestISNULL values (2,'BBB')
GO

declare @name varchar(50)
set @name=null
select * from TestISNULL where name = ISNULL(@name,name)

--结果是两行没有任何问题
/*
id          name
----------- ----------------------
1           AAA
2           BBB
(2 row(s) affected)
*/



--插入一条数据
insert into TestISNULL values (3,null)
GO

select * from TestISNULL
--全部结果是三行
/*
id          name
----------- --------------------
1           AAA
2           BBB
3           NULL
(3 row(s) affected)
*/



--继续用isnull的方式处理变量做查询
declare @name varchar(50)
set @name=null
select * from TestISNULL where name = ISNULL(@name,name)
--结果是两行，你明白为什么吗
/*
id          name
----------- ----------------------
1           AAA
2           BBB

(2 row(s) affected)

　　　　究竟为什么呢？好吧，下面应该很清楚了

　　　　

 

　2，SQL Server 对数据类型优先级引起Case When 的一些语法错误

 

　　　　继续用上面的表验证另外一个小语法问题，某些逻辑处理数据的时候，字段为null的时候，将结果统一标记为一种数值，如下

　　　　当然，这样是通不过的，提示将AAA转换为数值的时候失败

　　　　这种问题本质上是：会将低优先级的varchar转换为高优先级的int进行对比

　　　　但是在将字符转换为数值型的时候，就会出现转换失败的情况

　　　　这里就涉及到SQL Server数据类型优先级的问题，有兴趣的自行MSDN

　　　　

SELECT id,
       CASE
         WHEN name IS NULL THEN 0
         ELSE name
       END AS name
FROM   TestISNULL

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'AAA' to data type int.

 

解决办法就是统一数据类型，注意字符0和数值0是不一样的，这样就不会引起数据类型优先级转换的问题

SELECT id,
       CASE
         WHEN name IS NULL THEN '0'
         ELSE name
       END AS name
FROM   TestISNULL

 当然这个问题的“变种”不仅限于case when，有各种各样的“变种”，

当遇到“Error converting data type *** to ***.”此类问题的时候，可以考虑是不是这个原因引起的，对快速定位问题可以起到帮助作用，避免在这些小逻辑上浪费太多时间。