How can you use IIf in Transact-SQL?

Next>>         SQL Server tutorial  

SQL Server - use of IIf in Transact-SQL - Feb 27, 2010 at 11:50 AM by Shuchi Gauri

How can you use IIf in Transact-SQL?

IIF is used through CASE.

Syntax:

SELECT
Employee.EmpID , Employee.CompanyName , Employee.Country,
CASE WHEN Country='Germany' THEN '0049 ' + Phone ELSE Phone END AS Phone FROM Employee

SQL Server - use of IIf in Transact-SQL - May 05, 2009 at 22:00 PM by Rajmeet Ghai

How can you use IIf in Transact-SQL?

IIf is most commonly used in Microsoft access to determine if another expression is true or false. Return values for either of the outputs can be specified. This feature of IIf has to be written in the form of CASE statements in transact-SQL. If iif is used, a syntax error is reported.

Below is a snippet using IIf function to add employee code prefix “SL” if the employee belongs to Sales department.

SELECT sales.salesID , sales.employeename , sales.department , IIf([department]="sales","SL " & [code],[code]) AS empcode FROM Sales

The above code, using CASE in transact-SQL needs to be written as:

SELECT sales.salesID , sales.employeename , sales.department , CASE WHEN department='sales' THEN 'SL ' + code ELSE code END AS code FROM Sales  

SQL Server - use of IIf in Transact-SQL - June 21, 2009 at 09:00 AM by Amit Satpute

How can you use IIf in Transact-SQL?

There is no IIf in SQL Server's Transact SQL language!

In IIF, a single expression can be evaluated at a time. This is not the case with CASE which is a powerful command in Transact-SQL language.

CASE is one of the most powerful commands in the Transact-SQL language. In contrast to IIf, where you only evaluate one logical expression at a time, this limitation does not exist for CASE. Try, for example, to put this in one single IIf expression:

SELECT Cust.CID , Cust.CName , Cust.Location , CASE Location WHEN 'Germany' THEN '0049 ' + Phone WHEN 'India' THEN '0091 ' + Phone ELSE Phone END AS Phone FROM Cust

Next>>

Also read

SQL Server Reporting Services

What can SQL Server Reporting Services do?
Explain the architecture of reporting services.
Describe Reporting Lifecycle.
What are the ways to tune Reporting Services?
How to schedule SSAS Database backup.
How to Generate an Auto Incremental Number in a SSIS Package?
Lookup's are a key component in SQL Server Integration Services (SSIS). Explain its purpose
How to unzip a File in SSIS?...........

What are the ways to code efficient transactions?

Answer - Database users can have permission to execute a stored procedure without being....

What are the events recorded in a transaction log?

Answer - The start and end of each transaction......

Describe when checkpoints are created in a transaction log.

Answer - It is created when CHECKPOINT statement is executed.......

Define transaction and transaction isolation levels.

Answer - A transaction is a set of operations that works as a single unit. The ransactions can be categorized into explicit, autocommit, and implicit....