Home

Transact-SQL Keywords: ELSE

 

Introduction

The IF condition we used above is appropriate when you only need to know if an expression is true. There is nothing to do in other alternatives. Consider the following code:

DECLARE @DateHired As datetime2,
	@CurrentDate As datetime2
SET @DateHired = N'1996/10/04'
SET @CurrentDate  = N'2007/04/16'
IF @DateHired > @CurrentDate
	PRINT N'You have the experience required for a new promotion'
GO

This would produce:

IF...ELSE

Notice that, in case the expression to examine produces a false result, there is nothing to do. Sometimes this will happen.

CASE...WHEN...THEN...ELSE

In most cases, you may know the only types of value that would be submitted to a CASE statement. In some other cases, an unpredictable value may be submitted. If you anticipate a value other than those you are aware of, the CASE statement provides a "fit-all' alternative by using the last statement as ELSE. In this case, the formula of the CASE statement would be:

CASE Expression
	WHEN Value1 THEN Result
	WHEN Value2 THEN Result
	WHEN Value_n THEN Result
	
	ELSE Alternative
END

The ELSE statement, as the last, is used when none of the values of the WHEN statements fits. Here is an example:

DECLARE @CharGender Char(1),
	@Gender  Varchar(20);
SET @CharGender = 'g';
SET @Gender = 
	CASE @CharGender
		WHEN N'm' THEN N'Male'
		WHEN N'M' THEN N'Male'
		WHEN N'f' THEN N'Female'
		WHEN N'F' THEN N'Female'
		ELSE 'Unknown'
	END;

SELECT N'Student Gender: ' + @Gender;
GO

This would produce:

CASE...WHEN...THEN...ELSE

If you don't produce an ELSE statement but a value not addressed by any of the WHEN statements is produced, the result would be NULL. Here is an example:

CASE...WHEN...THEN...ELSE

This means that it is a valuable safeguard to always include an ELSE sub-statement in a CASE statement.


Home Copyright © 2007-2011 FunctionX.com, Inc.