Besides using functions as we saw in the previous two
lessons, you can create expressions that represent a combination of values,
variables, and operators. To support expressions, Transact-SQL provides
operators other than, or in addition to, those we saw in Lesson 4.

A comparison is a Boolean operation that produces a
true or a false result, depending on the values on which the comparison
is performed. A comparison is performed between two values of the same
type; for example, you can compare two numbers, two characters, or the
names of two cities.

To support comparisons, Transact-SQL provides all
necessary operators.

Equality Operator =

To compare two variables for equality, use the = operator. Its formula is:

Value1 = Value2

The equality operation is used to find out whether two
variables (or one variable and a constant) hold the same value. From our syntax, the compiler would compare the value of Value1 with that of Value2. If Value1 and Value2 hold the same value, the comparison produces a true result. If they are different, the comparison renders
false or 0.

The equality operation can be illustrated as follows:

Not Equal <>

As opposed to equality, to find out if two values are
not equal, use the <> operator. Its formula is:

Value1 <> Value2

The <> is a binary operator (like all logical
operators) that is used to compare two values. The values can come from
two variables as in Variable1 <> Variable2. Upon comparing the
values, if both hold different values, the comparison produces a true or
positive value. Otherwise, the comparison renders false or a null value.

It can be illustrated as follows:

Notice that the Not Equal operator <> is the opposite
to the Equality operator =.

Less Than <

To find out whether one value is lower than another,
use the < operator. Its formula is:

Value1 < Value2

The value held by Value1 is compared to that of
Value2. As it would be done with other operations, the comparison can be
made between two variables, as in Variable1 < Variable2. If the value
held by Variable1 is lower than that of Variable2, the comparison produces
a true or positive result.

The Less Than operator "<" can be
illustrated as follows:

Less Than Or Equal To <=

The equality and the Less Than operators can be combined to compare
two values. This allows you to know if two values are the same or if the
first is less than the second. The operator used is <= and its formula is:

Value1 <= Value2

If both Value1 and Value2 hold the same value, the result is
true or positive. If the left operand, in this case Value1, holds a
value lower than the second operand, in this case Value2, the
result is still true.

A <= operation can be illustrated as follows:

Greater Than >

To find out if one value is strictly greater than
another, you can use the > operator. Its formula is:

Value1 > Value2

Both operands, in this case Value1 and Value2, can be
variables or the left operand can be a variable while the right operand is
a constant. If the value on the left of the > operator is greater than
the value on the right side or a constant, the comparison produces a true
or positive value. Otherwise, the comparison renders false or null.

The > operator can be illustrated as follows:

Notice that the > operator is the opposite to <=.

Greater Than or Equal To >=

The greater than and the equality operators can be
combined to produce an operator as follows: >=. This is the
"greater than or equal to" operator. Its formula is:

Value1 >= Value2

A comparison is performed on both operands: Value1 and
Value2. If the value of Value1 and that of Value2 are the same, the
comparison produces a true or positive value. If the value of the left
operand is greater than that of the right operand, the comparison
produces true or positive also. If the value of the left operand is
strictly less than the value of the right operand, the comparison produces
a false or null result. This can be illustrated as follows:

Notice that the >= operator is the opposite to <.

Conditional Statements

Introduction

A condition statement is an expression you formulate to
evaluate it. Most of the time, the statement is written so that, when
evaluated, it can produce a result of true or false, then, depending on the
outcome, you can take action. A condition is usually written as simple as
possible to make it clear to you and the SQL interpreter. Although the
interpreter never gets confused, if you create a difficult statement, you may
receive an unpredictable result.

In the next few sections, we will review the keywords and
formulas that Transact-SQL provides to help you formula clear expressions.
Expressions usually start with a keyword, followed by the expression itself.
After the expression, you can tell the interpreter what to do. The statement may
appear as follows:

Keyword Expression
Statement

BEGIN...END

With the above formula, we will always let you know what
keyword you can use, why, and when. After the expression, you can write the
statement in one line. This is the statement that would be executed if/when the Expression
of our formula is satisfied. In most cases, you will need more than one line of
code to specify the Statement. As it happens, the interpreter considers
whatever comes after the Statement as a unit but only the line immediately after
the Expression. To indicate that your Statement covers more than
one line, start it with the BEGIN keyword. Then you must use the END
keyword to indication where the Statement ends. In this case, the formula
of a conditional statement would appear as follows:

Keyword Expression
BEGIN
Statement Line 1
Statement Line 2
Statement Line n
END

You can still use the BEGIN...END combination even if
your Statement covers only one line:

Keyword Expression
BEGIN
Statement
END

Using the BEGIN...END combination makes your code
easier to read because it clearly indicates the start and end of the Statement.

IF a Condition is True

Probably the primary comparison you can perform on a
statement is to find out whether it is true. This operation is performed using
an IF statement in Transact-SQL. Its basic formula is:

IF ConditionStatement

When creating an IF statement, first make sure you provide a
Condition expression that can be evaluated to produce true or false. To
create this Condition, you can use variables and the logical comparison
operator reviewed above.

When the interpreter executes this statement, it first
examines the Condition to evaluate it to a true result. If the Condition
produces true, then the interpreter executes the Statement. Here is an
example:

DECLARE @DateHired As DateTime,
@CurrentDate As DateTime
SET @DateHired = '1996/10/04'
SET @CurrentDate = GETDATE()
IF @DateHired < @CurrentDate
PRINT 'You have the experience required for a new promotion in this job'
GO

This would produce:

IF...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 DateTime,
@CurrentDate As DateTime
SET @DateHired = '1996/10/04'
SET @CurrentDate = GETDATE()
IF @DateHired > @CurrentDate
PRINT 'You have the experience required for a new promotion'
GO

This would produce:

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

CASE...WHEN...THEN

The CASE keyword is used as a conditional operator
that considers a value, examines it, and acts on an option depending on the
value. The formula of the CASE statement is:

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

In the following example, a letter that represents a student
is provided. If the letter is m or M, a string is created as Male. If the value
is provided as f or F, a string is created as Female:

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:

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.

WHILE

To examine a condition and evaluate it before taking
action, you can use the WHILE operator. The basic formula of this
statement is:

WHILE ExpressionStatement

When implementing this statement, first provide an Expression
after the WHILE keyword. The Expression must produce a true
or a false result. If the Expression is true, then the interpreter executes the
Statement. After executing the Statement, the Expression is
checked again. AS LONG AS the Expression
is true, it will keep executing the Statement. When or once the Expression
becomes false, it stops executing the Statement. This scenario can
be illustrated as follows:

Here is an example:

DECLARE @Number As int
WHILE @Number < 5
SELECT @Number AS Number
GO

To effectively execute a while condition, you
should make sure you provide a mechanism for the interpreter to get a
referenced value for the condition, variable, or expression being checked.
This is sometimes in the form of a variable being initialized although it
could be some other expression. Such a while condition could be
illustrated as follows: