ARTICLE

# IF and IF..ELSE condition use in the SQL Server

Posted by | April 05, 2010
Tags:
In this article,We will see how to use the IF and IF..ELSE condition in the SQL Server.

In this article,We will see how to use the IF and IF..ELSE condition in the SQL Server.

Boolean Algebra:-

In Boolean algebra, something is considered TRUE  when it holds a value. The value is also considered as 1 or Yes. By contrast, if something doesn't hold a value, it is considered non-existent and non-worthy of consideration. Such a thing has a value of FALSE, 0, or No. To retrieve such a value, you can just find out if the value of a field is existent or not.

The comparison for a True or False value is mostly performed on Boolean fields, such a case is the SPHome (which specifies whether a student lives in a single parent home) field of the Students table of the HighSchool database. If a record has a value of 1, the table considers that such a field is True. If the field has a 0 value, then it holds a FALSE value.

1. IF Condition:-

To find out whether a condition is true, you use the IF operator of Transact-SQL.

Its basic formula is:-

IF Condition

Statement

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.

Example:-

T-SQL has the "IF" statement to help with allowing different code to be executed based on the results of a condition. The "IF" statement allows a T-SQL programmer to selectively execute a single line or block of code based upon a Boolean condition.

There are two formats for the "IF" statement, both are shown below:

1.  IF <condition> <then code to be executed when condition true>.

2.  IF <condition> <then code to be executed when condition true>ELSE < else code to be executed when condition is false>.

Example:-

If  DATEPART(DW,GETDATE())=5

Begin

Print 'Sunday'

End

Output:-

2. IF..ELSE Condition:-

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.

Example:-

If  DATEPART(DW,GETDATE())=5

Begin

Print 'Sunday'

End

Else

Begin

Select 'Sorry,Sunday is ' + CONVERT(Varchar(1),(5-DATEPART(DW,GETDATE()))) + 'days away' AS ColumnShow

End

Output:-

3. 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.

Example:-

DECLARE @CharGender Char(10),

@Gender  Varchar(20);

SET @CharGender = 'f ';

SET @Gender =

CASE @CharGender

WHEN 'm' THEN 'Male'

WHEN 'M' THEN 'Male'

WHEN 'f ' THEN 'Female'

WHEN 'F' THEN 'Female'

ELSE 'Unknown'

END;

SELECT 'Student Gender: ' + @Gender AS GenderColumn;

GO

Output:-

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.

Example:-

DECLARE @CharGender Char(10),

@Gender  Varchar(20);

SET @CharGender = 'G';

SET @Gender =

CASE @CharGender

WHEN 'm' THEN 'Male'

WHEN 'M' THEN 'Male'

WHEN 'f ' THEN 'Female'

WHEN 'F' THEN 'Female'

ELSE 'Unknown'

END;

SELECT 'Student Gender: ' + @Gender AS GenderColumn;

GO

Output:-