I am going write a couple of 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 CHOOSE function today.
If you grew up like I did, you probably saw black and white re-runs of the Three Stooges on television.
The example below uses the RAND function, to get a random index value. The index value is used with the CHOOSE() function to return the culprit who broke our chair.
Many chairs were broken in the Three Stooges slapstick skits.
|
1 2 3 4 5 6 7 8 |
-- -- Choose Function -- -- Who broke the chair? declare @idx int = CAST((RAND() * 3) AS INT) + 1; select @idx as idx, CHOOSE(@idx, 'Moe', 'Larry', 'Curley') as Culprit 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 26 |
output: Beginning execution loop idx Culprit ----------- ------- 1 Moe idx Culprit ----------- ------- 2 Larry idx Culprit ----------- ------- 3 Curley idx Culprit ----------- ------- 2 Larry idx Culprit ----------- ------- 3 Curley Batch execution completed 5 times. |
The function takes an index value N and a list of items. It returns the Nth item in the list.
Please note the number five after the key word GO. This tells the transaction processor to run the commands (batch) five times.
Next time, I will be talking about the immediate if, IIF function.
