ISNULL()函式對於資料型態的隱性規則

情境

想在Sql server要用 特定學生姓名 去查閱此學生是否已註冊(已註冊為1,未註冊為0)若無此學生資料則回傳-1

SQL 資料表與型態範例

1.先創造資料表名為: Students

2.定義資料表欄位與其型態

3.新增幾個學生範例供參考

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