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.
|
1 2 3 4 5 6 7 8 9 10 11 |
IF (Expression ) BEGIN -- If the condition is TRUE then execute the following statement True Statements; END ELSE BEGIN -- If the condition is False then execute the following statement False Statements END |
We can understand SQL IF Statement using the following flow chart.
- 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.
|
1 2 3 4 |
IF(1 = 1) PRINT 'Executed the statement as condition is TRUE'; ELSE PRINT 'Executed the statement as condition is FALSE'; |
If we change the condition in the Boolean expression to return FALSE, it prints statement inside ELSE.
|
1 2 3 4 |
IF(2<=0) PRINT 'Executed the statement as condition is TRUE'; ELSE PRINT 'Executed the statement as condition is FALSE'; |
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.
|
1 2 3 4 5 |
DECLARE @StudentMarks INT= 91; IF @StudentMarks >= 80 PRINT 'Passed, Congratulations!!'; ELSE PRINT 'Failed, Try again '; |
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
|
1 2 3 4 5 6 7 |
DECLARE @StudentMarks INT= 91; IF @StudentMarks >= 90 PRINT 'Congratulations, You are in Merit list!!'; IF @StudentMarks >= 80 PRINT 'Congratulations, You are in First division list!!'; ELSE PRINT 'Failed, Try again '; |
In this example, student marks 91% satisfy the conditions for both SQL IF statements, and it prints a message for both SQL IF statements.
We do not want the condition to satisfy both SQL IF statements. We should define the condition appropriately.
|
1 2 3 4 5 6 7 |
DECLARE @StudentMarks INT= 91; IF @StudentMarks >= 90 PRINT 'Congratulations, You are in Merit list!!'; IF @StudentMarks >= 80 and @StudentMarks < 90 PRINT 'Congratulations, You are in First division list!!'; ELSE PRINT 'Failed, Try again '; |
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
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.
|
1 2 3 |
DECLARE @StudentMarks INT= 95; IF @StudentMarks >= 90 PRINT 'Congratulations, You are in Merit list!!'; |
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 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.
|
1 2 3 4 5 6 7 8 9 |
DECLARE @sales INT; SELECT @sales = SUM(OrderQty * UnitPrice) FROM [AdventureWorks2017].[Sales].[SalesOrderDetail]; IF @sales > 100000000 SELECT * FROM [AdventureWorks2017].[Sales].[SalesOrderDetail]; ELSE SELECT * FROM [AdventureWorks2017].[Sales].[SalesOrderHeader]; |
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.
|
1 2 3 4 5 6 7 8 9 |
DECLARE @StudentMarks INT= 70; IF @StudentMarks >= 90 BEGIN PRINT 'Congratulations, You are in Merit list!!'; END; ELSE BEGIN PRINT 'Failed, Try again '; END; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @StudentMarks INT= 70; IF @StudentMarks >= 90 BEGIN PRINT 'Congratulations, You are in Merit list!!'; Print 'Second statement.' END; ELSE BEGIN PRINT 'Failed,Try again '; Print 'Second ELSE statement' END; |
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023