搜尋本站文章

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 陳述式的所有行為,從遠端處理到其他伺服器。





參考資料

IIF (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/hh213574.aspx

CASE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms181765.aspx

運算式範例 (報表產生器及 SSRS)
http://technet.microsoft.com/zh-tw/library/ms157328

IIf 函式 -- Visual Studio 2008
http://msdn.microsoft.com/zh-tw/library/27ydhh0d(v=vs.90).aspx

IIf 函數 -- Office Access 2007
http://office.microsoft.com/zh-tw/access-help/iif-function-HA001228853.aspx