安全的使用T-SQL中的ISNULL函数

在SQL Server中ISNULL函数是如此的有用和常用,然而我们却经常忽略它的特性而误用，以致产生意想不到的结果。

请看示例1：

 

DECLARE @STR VARCHAR(4)

SET @STR=NULL

SELECT ISNULL(@STR,'A VERY LONG DEFAULT STRING')

 

也许我们期望的结果是

-----------------------

GIVEN STRING IS NULL

 

(1 row(s) affected)

 

而实际上结果是：

----

GIV

 

(1 row(s) affected)

 

再看示例2：

DECLARE @sInt SMALLINT

SET @sInt=NULL

SELECT ISNULL(@sInt,65535)

 

我们期望输出的结果是：

 

-----------

65535

 

(1 row(s) affected)

而实际上该语句会报错，错误信息如下：

 

------

Msg 220, Level 16, State 1, Line 3

Arithmetic overflow error for data type smallint, value = 65535.

 

查看SQL Server联机文档中队ISNULL的说明如下：

语法

---

复制

 

ISNULL ( check_expression , replacement_value )

参数

---

check_expression

将被检查是否为 NULL 的表达式。check_expression 可以为任何类型。

replacement_value

当 check_expression 为 NULL 时要返回的表达式。replacement_value 必须是可以隐式转换为 check_expresssion 类型的类型。

返回类型

---

返回与 check_expression 相同的类型。

注释

---

如果 check_expression 不为 NULL，则返回它的值；否则，在将 replacement_value 隐式转换为 check_expression 的类型（如果这两个类型不同）后，则返回前者。

这里需要特别注意的是返回类型的说明，“返回与 check_expression 相同的类型。”

正是因为返回时会将replacement_value隐式转换为check_expression导致示例1中的字符串被截断，而导致示例2中抛出算术运算溢出异常。

为了安全的返回我们期望的结果，示例1、2可以分别改为：

示例1：

DECLARE @STR VARCHAR(4)

SET @STR=NULL

SELECT ISNULL(CONVERT(VARCHAR(MAX),@STR), 'A VERY LONG DEFAULT STRING')

 

示例2：

DECLARE @sInt SMALLINT

SET @sInt=NULL

SELECT ISNULL(CONVERT(INT,@sInt),65535)

 

也可以简化处理如下：

示例1：

DECLARE @STR VARCHAR(4)

SET @STR=NULL

SELECT ISNULL(CONVERT(STR + 'A VERY LONG DEFAULT STRING', 'A VERY LONG DEFAULT STRING')

 

示例2：

DECLARE @sInt SMALLINT

SET @sInt=NULL

SELECT ISNULL(@sInt + 65535,65535)