Logical Functions and Expressions ado.net tutorials

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.

CASE Expression

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
  

IIF Function

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
  


Share This

Home | About | Contact | Privacy Policy

Copyright visual-basic-tutorials.com 2017 - All Rights Reserved.