德瑞克：SQL Server 學習筆記: SQL Server 2012 ：認識 IIF 邏輯函數

2012-07-09

SQL Server 2012 ：認識 IIF 邏輯函數

適用版本：SQL Server 2012。

IIF 邏輯函數
根據布林運算式求得的解為 true 或 false 而傳回兩值之一。

雖然 IIF() 邏輯函數是 SQL Server 2012版本新增加的功能，但若你已經使用過 VBA(Visual Basic for Applications) 或是 SSRS 決策函數等，那你可能已經使用過 IIF 函數。

語法：

IIF ( boolean_expression, true_value, false_value )

請參考以下的範例程式碼

EX1：認識 IIF() 邏輯函數與 CASE() 運算式

-- 使用 CASE() 運算式
DECLARE @c1 int =10, @c2 int = 20

SELECT 
 CASE WHEN (@c1 < @c2) THEN N'成立'
  ELSE N'不成立'
 END N'判斷結果'
GO

-- 使用 IIF() 邏輯函數
DECLARE @c1 int =10, @c2 int = 20

SELECT IIF(@c1 < @c2, N'成立', N'不成立') N'判斷結果'
GO

-- 01_認識 IIF() 邏輯函數與 CASE() 運算式

EX2：IIF() 邏輯函數與 CASE() 運算式：查詢資料表

-- 使用 CASE() 運算式
USE Northwind
GO
SELECT ProductID, ProductName, UnitsInStock, 
 CASE 
  WHEN UnitsInStock>50 THEN N'高於'
  ELSE N'低於'
 END N'安全庫存數量(50)'
FROM Products
GO

-- 使用 IIF() 邏輯函數
SELECT ProductID, ProductName, UnitsInStock, 
 IIF( UnitsInStock>50, N'高於', N'低於') N'安全庫存數量(50)'
FROM Products
GO

-- 02_IIF() 邏輯函數與 CASE() 運算式：查詢資料表

EX3：巢狀 IIF (Nested IIF)

-- 巢狀 IIF (Nested IIF)
--執行邏輯判斷後，回傳：老爺、夫人、少爺、小姐
DECLARE @gender varchar(1), @maritalstatus varchar(1)
SET @gender ='M'
SET @maritalstatus = 'M'

SELECT IIF(@gender ='M', IIF(@maritalstatus='M','老爺','少爺' ), IIF(@maritalstatus='M','夫人','小姐' ));
GO

-- 03_巢狀 IIF (Nested IIF)

-- CASE() 運算式 與 CTE(一般資料表運算式)
-- 執行邏輯判斷後，回傳：老爺、夫人、少爺、小姐
WITH tPerson AS
 (
 SELECT * 
 FROM ( VALUES (1,'M','M'),  (2,'M','F'), (3,'F','M'), (4,'F','F')) t(eid, gender, maritalstatus)
 ) 
SELECT  eid, gender, maritalstatus,
 CASE 
  WHEN gender ='M' AND maritalstatus='M' THEN N'老爺'
  WHEN gender ='M' AND maritalstatus='F' THEN N'少爺'
  WHEN gender ='F' AND maritalstatus='M' THEN N'夫人'
  WHEN gender ='F' AND maritalstatus='F' THEN N'小姐'
 END N'稱呼'
FROM tPerson;
GO

-- 執行巢狀 IIF 與 CTE(一般資料表運算式)
-- 邏輯判斷後，回傳：老爺、夫人、少爺、小姐
WITH tPerson AS
 (
 SELECT * 
 FROM ( VALUES (1,'M','M'),  (2,'M','F'), (3,'F','M'), (4,'F','F')) t(eid, gender, maritalstatus)
 ) 
SELECT  eid, gender, maritalstatus,
 IIF(gender ='M', IIF(maritalstatus='M','老爺','少爺' ), IIF(maritalstatus='M','夫人','小姐' )) N'稱呼'
FROM tPerson;
GO

-- 04_執行巢狀 IIF _CASE() 運算式與 CTE(一般資料表運算式)

EX4：IIF() 邏輯函數：錯誤 NULL

-- 包含 NULL 常數的 IIF
SELECT IIF ( 10 > 20, NULL, NULL ) N'判斷結果';
GO
/*
錯誤訊息：
訊息 8133，層級 16，狀態 1，行 1
CASE 規格中的結果運算式至少要有一個是 NULL 常數以外的運算式。
*/

-- 05_IIF() 邏輯函數：錯誤 NULL

-- 若是改由變數方式：包含 NULL 參數的 IIF
DECLARE @c1 INT = NULL;
DECLARE @c2 INT = NULL;

SELECT IIF ( 1 > 20, @c1, @c2) N'判斷結果';
GO
/*
回傳：NULL
*/

-- 06_IIF() 邏輯函數：回傳 NULL

EX5：IIF() 邏輯函數：回傳式的運算有問題時

-- 布林運算式為真，回傳第一個運算式
-- 其中，第二個運算式會產生分母為 0 的錯誤
DECLARE @c1 int =200, @c2 int=0

SELECT IIF(10>5, @c1+@c2, @c1/@c2)
GO

-- 07_布林運算式為真，回傳第一個運算式

-- 布林運算式為假，回傳第二個運算式
-- 其中，第二個運算式會產生分母為 0 的錯誤
DECLARE @c1 int =200, @c2 int=0

SELECT IIF(10>50, @c1+@c2, @c1/@c2)
GO
/*
錯誤訊息：
訊息 8134，層級 16，狀態 1，行 3
發現除以零的錯誤。
*/

-- 08_布林運算式為假，回傳第二個運算式

認識 IIF 邏輯函數

IIF 邏輯函數
根據布林運算式求得的解為 true 或 false 而傳回兩值之一。

語法：

IIF ( boolean_expression, true_value, false_value )

引數說明

(1) boolean_expression
有效的布林運算式。
此引數若不是布林運算式，將會引發語法錯誤。

(2) true_value
當 boolean_expression 求得的解為 true 時所要傳回的值。

(3) false_value
當 boolean_expression 求得的解為 false 時所要傳回的值。

傳回類型
從 true_value 及 false_value 的類型中，傳回優先順序最高的資料類型。

注意事項

IIF 是一種編寫 CASE 陳述式的簡略方法。
其會求得第一個引數所傳遞之布林運算式的解，然後依據求解結果，傳回另外兩個引數之一。

換言之，如果布林運算式為 true，即會傳回 true_value；如果布林運算式為 false 或不明，即會傳回 false_value。
true_value 及 false_value 可以是任何類型。

套用到布林運算式、NULL 處理及傳回類型之 CASE 陳述式的規則，也同樣會套用至 IIF。

IIF 轉換為 CASE 的事實，對此函數的其他方面行為也有影響。

由於 CASE 陳述式最多只可巢狀化到層級 10，因此 IIF 陳述式最多也只可巢狀化至層級 10。

此外，IIF 會以語意相等之 CASE 陳述式，並以遠端處理之 CASE 陳述式的所有行為，從遠端處理到其他伺服器。