CHOOSE and IIF in Sql server

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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s