Transact-SQL Keywords: ELSE



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'

This would produce:


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


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

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'

SELECT N'Student Gender: ' + @Gender;

This would produce:


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:


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


Home Copyright © 2007-2009 FunctionX, Inc.