Logical Functions and Expressions
Tags: VB.NET, VB 2008, VB 2010, VB 2012, VB 2013
This lesson describes how to use IIF function and CASE expression
As you've seen in the previous tutorials, T-SQL contains numerous functions that allow you to add conditional expressions to your queries. You can return a value depending on another value or the results of an expression, and these techniques are very similar to using IF...THEN, CASE or SWITCH in other programming languages.
You usually use the CASE expression to evaluate a list of expressions and return the first one that evaluates to true. For example, you may need to display the financial results based on one of the date column(s) in the table.
The Syntax of the CASE expression is as follows:
CASE [test expression] WHEN [comparison expression] THEN [return value] ELSE [another value] END
To write a simple CASE expression, you need an expression that you want to evaluate, often a column name, and a list of possible values:
CASE Balance WHEN Balance > 0 THEN 'True' ELSE 'False' END AS Active
The IIF function is just an easier method of writing a simple CASE expression and was introduced in SQL Server 2012, which means that it is pretty new. You can use an IIF function to return a result based on whether a Boolean expression is true or false, so to create an expression with the IFF function, you need a Boolean expression and the values to return based on the results.
The Syntax of the IIF function is as follows:
IIF ( [boolean expression], [true value], [false value] )
Here is a basic example for using IIF function:
SELECT IIF (Balance > 0, 'True', 'False') AS Active FROM Accounts WHERE UserID = @UserID