CHOOSE and IIF in Sql server


  • 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

FROM [Production].[Product] p
WHERE p.ProductID = 680

--this will return StandardCost


  • 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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s