ISNULL()函式对于资料型态的隐性规则

情境

想在Sql server要用 特定学生姓名 去查阅此学生是否已注册(已注册为1,未注册为0)若无此学生资料则回传-1

SQL 资料表与型态範例

1.先创造资料表名为: Students

2.定义资料表栏位与其型态

栏位名称资料型态student_idbigintstudent_namevarchar(30)is_registeredbit

3.新增几个学生範例供参考

student_idstudent_nameis_regesitered1Gary02Max03Tina14Linda0

SQL语法与遇到问题

ISNULL ( 检查运算式 , 取代值 )
检查运算式内结果是否为null,若为null则用取代值取代

把找出is_registered的sql贴到检查运算式,取代值设为-1表示没有资料

DECLARE @IsRegistered smallint;DECLARE @Name nvarchar(30)='Cleo';SET @IsRegistered= ISNULL((SELECT TOP(1) is_registered FROM Students WITH(NOLOCK) WHERE student_name =@Name),-1);SELECT @IsRegistered;

因为没有Cleo这位学生,故以为这样直观的写法会是预期的结果:-1
但得到的@IsRegistered却是1

原因

后来查了微软文件关于ISNULL的功能解释

其中提到了

check_expression
为要检查 NULL 的运算式。 check_expression 可为任何类型。
replacement_value
为 check_expression 是 NULL 时，要传回的运算式。 replacement_value 必须是能够隐含转换成 check_expression 类型的类型。

replacement_value 必须是能够 隐含转换 成 check_expression 类型的类型
也就是说上述的sql语法,因为我们的check_expression

SELECT TOP(1) is_registered FROM Students WITH(NOLOCK) WHERE student_name =@Name

取出来的is_registered在db内是设定成 bit (只有0或1的选项)
而我们的取代值-1不可能转换成bit...

解决方法

其实解决方法很简单
把得出来的is_registered用smallint型态的变数取存入再进行ISNULL()自我判定结果就好

SET @IsRegistered= (SELECT TOP(1) is_registered FROM Students WITH(NOLOCK) WHERE student_name =@Name);SET @IsRegistered = ISNULL(@IsRegistered,-1);

就是这么简单
并不是太複杂或是深奥的观念
但是有时候这种基础规则没有好好记得反而增加找问题的时间成本...

tags: sql database