Rajendra Gupta
a variable in a Boolean expression and multiple IF statements

SQL IF Statement introduction and overview

May 20, 2019 by

This article explores the useful function SQL IF statement in SQL Server.

Introduction

In real life, we make decisions based on the conditions. For example, look at the following conditions.

  • If I get a performance bonus this year, I will go for international vacation or else I’ll take a domestic vacation
  • If the weather becomes good, I will plan to go on a bike trip or else I won’t

In these examples, we decide as per the conditions. For example, if I get a bonus then only I will go for an international vacation else I will go for domestic vacations. We need to incorporate these conditions-based decisions in programming logic as well. SQL Server provides the capability to execute real-time programming logic using SQL IF Statement.

Syntax

In the following SQL IF Statement, it evaluates the expression, and if the condition is true, then it executes the statement mentioned in IF block otherwise statements within ELSE clause is executed.

We can understand SQL IF Statement using the following flow chart.

Flow Chart of SQL IF Statements

  • The condition in SQL IF Statement should return a Boolean value to evaluate
  • We can specify a Select statement as well in a Boolean expression, but it should enclose in parentheses
  • We can use BEGIN and END in the IF Statement to identify a statement block
  • The ELSE condition is optional to use

Let’s explore SQL IF Statement using examples.

Example 1: IF Statement with a numeric value in a Boolean expression

In the following example, we specified a numeric value in the Boolean expression that is always TRUE. It prints the statement for If statement because the condition is true.

SQL IF Statement with a numeric value in a Boolean expression

If we change the condition in the Boolean expression to return FALSE, it prints statement inside ELSE.

SQL IF Statement with a numeric value in a Boolean expression with FALSE condition

Example 2: IF Statement with a variable in a Boolean expression

In the following example, we use a variable in the Boolean expression to execute the statement based on the condition. For example, if a student obtained more than 80% marks, he passes an examination else, he is failed.

variable in a Boolean expression

Example 3: Multiple IF Statement with a variable in a Boolean expression

We can specify multiple SQL IF Statements and execute the statement accordingly. Look at the following example

  • If a student gets more than 90% marks, it should display a message from the first IF statement
  • If a student gets more than 80% marks, it should display a message from the second IF statement
  • Otherwise, it should print the message mentioned in ELSE statement

In this example, student marks 91% satisfy the conditions for both SQL IF statements, and it prints a message for both SQL IF statements.

a variable in a Boolean expression and multiple IF statements

We do not want the condition to satisfy both SQL IF statements. We should define the condition appropriately.

In the following screenshot, we can see second IF condition is TRUE if student marks are greater than or equal to 80% and less than 90%.

In the output, we can see the following

  • First, IF statement condition is TRUE. It prints the message inside the IF statement block
  • Second, IF statement condition is FALSE, it does not print the message inside IF statement block
  • It executes the ELSE statement and prints the message for it. In this case, we have two SQL IF statements. The second IF statement evaluates to false, therefore, it executes corresponding ELSE statement

a variable in a Boolean expression and multiple IF statements

We need to be careful in specifying conditions in multiple SQL IF statement. We might get an unexpected result set without proper use of SQL IF statement.

Example 4: IF Statement without ELSE statement

We specified above that Else statement is optional to use. We can use SQL IF statement without ELSE as well.

In the following, the expression evaluates to TRUE; therefore, it prints the message.

example without ELSE statement

If the expression evaluates to FALSE, it does not return any output. We should use ELSE statement so that if an evaluation is not TRUE, we can set default output.

example without ELSE statement

Example 5: IF Statement to execute scripts

In the above examples, we print a message if a condition is either TRUE or FALSE. We might want to execute scripts as well once a condition is satisfied.

In the following example, if sales quantity is greater than 100000000, it should select records from SalesOrderDtails table.

If the sales quantity is less than 100000000, it should select records from the SalesOrderHeader table.

Example 6: IF with BEGIN and END block

We can use BEGIN and END statement block in a SQL IF statement. Once a condition is satisfied, it executes the code inside the corresponding BEGIN and End block.

BEGIN and END block

We can specify multiple statements as well with SQL IF statement and BEGIN END blocks. In the following query, we want to print a message from two print statements once a condition is satisfied.

  • Note: We should have an END statement with corresponding BEGIN block.

BEGIN and END block examples with IF statement

Conclusion

In this article, we explored the SQL IF statement and its usage with examples. We can write real-time conditions-based code using SQL IF statements. If you had comments or questions, feel free to leave them in the comments below.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

1,339 Views