What is Logical functions

Another category of functions allows determine which of several values is to be returned. Logical functions evaluate an input expression, and return an appropriate value based on the result.

IIF

The IIF function evaluates a Boolean input expression, and returns a specified value if the expression evaluates to True, and an alternative value if the expression evaluates to False.

For example, consider the following query, which evaluates the address type of a customer. If the value is “Main Office”, the expression returns “Billing”. For all other address type values, the expression returns “Mailing”.

SQLCopy

SELECT AddressType,
      IIF(AddressType = 'Main Office', 'Billing', 'Mailing') AS UseAddressFor
FROM Sales.CustomerAddress;

The partial results from this query might look like this:

AddressType

UseAddressFor

Main Office

Billing

Shipping

Mailing

…

…

CHOOSE

The CHOOSE function evaluates an integer expression, and returns the corresponding value from a list based on its (1-based) ordinal position.

SQLCopy

SELECT SalesOrderID, Status,
CHOOSE(Status, 'Ordered', 'Shipped', 'Delivered') AS OrderStatus
FROM Sales.SalesOrderHeader;

The results from this query might look something like this:

SalesOrderID

Status

OrderStatus

1234

3

Delivered

1235

2

Shipped

1236

2

Shipped

1237

1

Ordered