Services

Interview Questions

SQL Server IF ELSE

The IF statement is a part of the control flow function in SQL Server. Usually, it is a decision-making statement in various programming languages that returns a value based on the given conditions . This statement executes the code written in IF block when the given condition evaluates to true and when the condition evaluates false, then the ELSE statement will be executed.

The IF Statement

The following are the syntax that illustrates the use of this statement in SQL Server :

IF boolean_expression BEGIN { statement_block }

In the above syntax, the statement_block in the BEGIN...END block is executed when the boolean_expression is satisfied with the condition. Otherwise, this block is skipped, and the program control is moved to the statement after the END keyword. We should know that if the expression contains a SELECT statement, we need to enclose them in parentheses .

Example

Let us see the example to understand the IF statement without ELSE block. The below example will display the result when the condition is satisfied. Otherwise, the program control moved to the statement after the END keyword, if any.

DECLARE @Marks INT = 65 ; IF @marks >= 45 BEGIN PRINT 'Congratulations! You pass the Examination';

Executing the statement will give the below output:

Now, we will demonstrate it on the below ' Student' table having the following data:

The below is another example that gets the total marks of a selected student from the ' Student' table in the sample database and then prints a message if it is greater than 400 .

BEGIN DECLARE @Total_Marks INT; SELECT @Total_Marks = total_marks FROM Student WHERE age>25; SELECT @Total_Marks; IF @Total_Marks > 400 BEGIN PRINT 'Congratulations! You pass the Examination';

We will get the below output:

If we want to see the above output message, we should click the Messages tab:

The IF-ELSE Statement

In the real-world scenario, we need to perform some action whenever the condition in the IF statement is TRUE or FALSE. In this case, the IF…ELSE statement is useful. This statement executes the ELSE statement block when the condition in the IF clause is evaluated FALSE.

The following are the syntax that illustrates the use of the IF ELSE statement in SQL Server :

IF expression BEGIN Statement block -- It executes when the IF clause expression is TRUE. BEGIN Statement block -- It executes when the IF clause expression is FALSE.

Example

Let us see the example to understand the IF statement with ELSE block. The below example will display the message " Congratulations! You pass the Examination " when the IF condition is satisfied. Otherwise, display " You are failed! Better Luck Next Time ".

DECLARE @Marks INT; SET @Marks = 65; IF @marks < 45 BEGIN PRINT 'Congratulations! You pass the Examination'; BEGIN PRINT 'You are Failed! Better Luck Next Time';

Executing the statement will give the below output. Here, the marks variable is 65 , and the condition (65<45) is not satisfied. Therefore, the message inside the ELSE block is displayed:

We will get this output because the condition (65>45) is satisfied. Therefore, the message inside the IF block is displayed:

Now, we will demonstrate the IF ELSE statement on the above ' Student' table. In this example, we are going to check whether the student total marks is greater than or equal to 400 or not as follows:

When the IF condition is TRUE, we will get the student records whose total marks are greater than or equal to 550.

If the condition is FALSE, we will get the student records whose total marks are less than 550.

Here is the program:

DECLARE @Marks INT; SET @Marks = 600 ; IF @Marks >= 550 BEGIN SELECT id, name, gender, age, total_marks FROM Student WHERE total_marks >= 550 ORDER BY age ASC BEGIN SELECT id, name, gender, age, total_marks FROM Student WHERE total_marks < 550 ORDER BY age ASC

In this code, we have specified the @Marks variable to 600 , and the condition (600 >= 550) is satisfied. Therefore, we will get the output where student records whose total marks are greater than or equal to 550 are displayed.

If we changed the @Marks variable to 500 and the condition (500 >= 550) becomes false. Therefore, we will get the output where student records whose total marks are less than 550 are displayed.

Nested IF ELSE Statement

Unlike other programming languages, we can nest an IF...ELSE statement inside another IF...ELSE statement in SQL Server. Let us demonstrate it with the following example:

DECLARE @age INT; SET @age = 6; IF @age < 18 PRINT 'You are underage'; BEGIN IF @age < 50 PRINT 'You are below 50'; PRINT 'You are senior cetizen';

In this example, we are going to check whether the age is underage, below 50, or senior citizen as follows:

If the value of the @age variable is below 18 , it will print the person is underage .

If the condition is FALSE, the ELSE part will be executed that has a nested IF…ELSE.

If the value of the @age variable is under 50 , it will print below 50 . Finally, if no condition is satisfied, it will print senior citizens .

Here is the result:

This article gives a complete overview of how to use the SQL Server IF ELSE statement. Here we have learned:

Variables are objects that serve as placeholders.

The keyword BEGIN will be used to start a statement block, and the END keyword must be used to close it.

The use of ELSE in an IF... ELSE statement is optional.

It's also possible to nest an IF...ELSE statement inside another IF...ELSE statement. However, nesting an IF statement within another statement is bad practice because it makes the code difficult to read and maintain.

Next Topic SQL Server ISNULL Function