I am going continuing my short articles or tidbits on Transaction SQL logical functions.
These functions were introduced with the SQL Server 2012 release. Both functions have their origins from Visual Basic for Applications (VBA) framework.
I think of these function as decision trees. Especially when they are nested to several levels.
If you have a kid like I do, you might find the decision tree for dropped food funny. Please see image to left.
I will be exploring the IIF function today.
The example below uses the RAND function, to set two variables with random numbers between 1 and 10. The immediate if function takes an expression to evaluate and two values. It returns the first value if the expression is TRUE; otherwise, it returns the second value.
|
1 2 3 4 5 6 7 8 |
-- -- Immediate If (IIF) Function -- DECLARE @a int = CAST((RAND() * 10) AS INT) + 1; DECLARE @b int = CAST((RAND() * 10) AS INT) + 1; SELECT @a as var_a, @b as var_b, IIF ( @a > @b, 'TRUE', 'FALSE' ) AS a_gt_b; GO 5 |
The output of each of the five batches is listed below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
output: Beginning execution loop var_a var_b a_gt_b ----------- ----------- ------ 9 2 TRUE var_a var_b a_gt_b ----------- ----------- ------ 7 2 TRUE var_a var_b a_gt_b ----------- ----------- ------ 6 1 TRUE var_a var_b a_gt_b ----------- ----------- ------ 3 1 TRUE var_a var_b a_gt_b ----------- ----------- ------ 5 6 FALSE Batch execution completed 5 times. |
Please note the number five after the key word GO. This tells the transaction processor to run the commands (batch) five times.
In a nutshell, this function can be replaced by the IF … ELSE control flow language. This syntax has been part of the T-SQL language for a long time.
