CHOOSE

• Returns a list item based on its location
• First parameter is index
• Next parameters are list

SELECT CHOOSE(2,'item1','item2' )
-- this will return item2

SELECT CHOOSE(1,'item1','item2' )
-- this will return item1

SELECT
CHOOSE(1,[StandardCost],[ListPrice])
FROM [Production].[Product] p
WHERE p.ProductID = 680

--this will return StandardCost

IIF

• Instant if
• Three parameters
  • Boolean expression
  • Return value if true
  • Return value if false

SELECT IIF([ListPrice] > 0, [ListPrice], [StandardCost]) AS 'price'
FROM [Production].[Product] p
-- if ListPrice is greater than 0 it returns Listprice. Otherwise returns StandardCost