Thursday, June 21, 2007

SQL IF...ELSE Statement

SQL IF...ELSE Statement used to test a condition. IF...ELSE Statement using in execution of a Transact-SQL statement (Store Procedure or T-SQL) and Trigger.
IF tests can be nested after another IF or following an ELSE. There is no limit to the number of nested levels.


IF condition is satisfied and the Boolean expression returns TRUE, it will executed IF Block Sql statement.
IF condtion is not satisfied and the Boolean expression returns FALSE, it will executed ELSE Block Sql Statement query.

Syntax for IF...ELSE
IF ( Boolean_expression )
BEGIN
Sql Statement Block
END
ELSE
BEGIN
Sql Statement Block
END


Below is simple example of IF...ELSE Statement With 1 IF...ELSE Block
For Boolean_expression part, you can replace with your condition to match with your Sql query.
It also can using Exists in the Condition to check the existence of a Sql Statement or Row.
IF (3>2)
   BEGIN
   SELECT 'Correct'
   END
ELSE
   BEGIN
   SELECT 'Wrong'
   END
Value = Correct

IF (3<2)
   BEGIN
   SELECT 'Correct'
   END
ELSE
   BEGIN
   SELECT 'Wrong'
   END

Value = Wrong

Below is another Example of More than 1 IF...ELSE Sql Statement Block
IF (10<2)
   SELECT 'Correct'
ELSE
   SELECT 'Wrong'
   IF ((10-2) > 5)
      SELECT 'More Than 5'
Value = Wrong
Value = More Than 5

If your IF...ELSE Block Sql Statement only have 1 Sql query, you no need to include the BEGIN...END. If your IF...ELSE Block Sql Statement have more than 1 Sql query, you need to include the BEGIN...END to let it executed all the Sql Query in your Sql Query Block.