Introduction:
In this article we will discuss:
- What is IF ELSE?
- Easy to complex examples for understanding IF ELSE
- Advantages & Disadvantages
- Important Questions for interview.
Definition:
IF ELSE is a conditional statement in SQL Server that allows you to execute different code blocks based on whether a certain condition is true or false. The IF ELSE statement evaluates a condition and then executes a block of code if the condition is true. If the condition is false, the statement executes a different block of code.
The basic syntax for the IF ELSE statement is as follows:
IF condition
BEGIN
— Code block to execute if condition is true
END
ELSE
BEGIN
— Code block to execute if condition is false
END;
In this syntax, the condition is evaluated first. If the condition is true, then the code block inside the first BEGIN and END statements is executed. If the condition is false, then the code block inside the second BEGIN and END statements is executed.
You can also use IF ELSE IF statements to evaluate multiple conditions. The syntax for this statement is as follows:
IF condition1
BEGIN
— Code block to execute if condition1 is true
END
ELSE IF condition2
BEGIN
— Code block to execute if condition2 is true
END
ELSE
BEGIN
— Code block to execute if all conditions are false
END;
In this syntax, each condition is evaluated in order. If condition1 is true, then the first code block is executed. If condition1 is false and condition2 is true, then the second code block is executed. If both conditions are false, then the code block inside the ELSE statement is executed.
The IF ELSE statement is commonly used in SQL Server stored procedures, functions, and triggers to perform conditional logic and make decisions based on data.
Examples:
Let’s see some examples to understand the IF statement without ELSE block.
DECLARE @productQuantity INT = 15;
IF @productQuantity < 10
BEGIN
PRINT ‘Product is low in stock.’;
END
PRINT ‘Checking product availability…’;
Executing the statement will give the below output:
In this example, the IF statement checks if the product quantity is less than 10. If the condition is true, then the code block inside the IF statement is executed, and the message “Product is low in stock.” is printed. If the condition is false, then the code block is skipped, and the message “Checking product availability…” is printed.
Note: that if the condition in the IF statement is not true, there is no code block to execute. In such cases, the execution flow simply continues to the next statement after the IF statement.
Easy examples of the IF ELSE statement in SQL Server to help you understand how it works:
Example: Checking if a student passed or failed an exam.
DECLARE @score INT = 75;
IF @score >= 60
BEGIN
PRINT ‘Student passed the exam.’
END
ELSE
BEGIN
PRINT ‘Student failed the exam.’
END;
In this example, the IF statement checks if the student’s score is greater than or equal to 60. If the score is 60 or above, then the student passed the exam, and the first code block is executed. If the score is below 60, then the student failed the exam, and the second code block is executed.
Example: Checking if a value is greater than a specific number.
DECLARE @num INT = 10;
IF @num > 5
PRINT ‘The value is greater than 5.’
ELSE
PRINT ‘The value is not greater than 5.’;
In this example, we declare a variable ‘@num’ and assign it the value 10. We then use an IF ELSE statement to check if the value of ‘@num’ is greater than 5. If it is, the message “The value is greater than 5.” is printed. Otherwise, the message “The value is not greater than 5.” is printed.
Let see, what happens if we change the value of ‘@num’ from 10 to 4 in the above code:
DECLARE @num INT = 4;
IF @num > 5
PRINT ‘The value is greater than 5.’
ELSE
PRINT ‘The value is not greater than 5.’;
The output will be “The value is not greater than 5.” because 4 is not greater than 5. So, the condition @num > 5 will evaluate to false, and the ELSE block will execute, printing the message “The value is not greater than 5.” to the console.
Intermediate level example:
Suppose we have a table Orders that stores information about orders placed by customers, with columns ‘OrderID’, ‘CustomerID’, ‘OrderDate’, and ‘TotalAmount’.
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
TotalAmount DECIMAL(10,2)
);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES
(1, 101, ‘2022-01-01’, 200.50),
(2, 102, ‘2022-01-02’, 300.00),
(3, 103, ‘2022-01-03’, 1000.00),
(4, 104, ‘2022-01-04’, 50.00),
(5, 101, ‘2022-01-05’, 750.00),
(6, 105, ‘2022-01-06’, 1500.00),
(7, 106, ‘2022-01-07’, 250.00),
(8, 107, ‘2022-01-08’, 800.00),
(9, 108, ‘2022-01-09’, 950.00),
(10, 101, ‘2022-01-10’, 350.00);
Example: What is the sum of all orders total amount and whether it is greater than, equal to, or less than 5000?
BEGIN
DECLARE @Amount INT;
SELECT @Amount = SUM(TotalAmount)
FROM Orders;
SELECT @Amount as TotalOrderSum;
IF @Amount > 5000
BEGIN
PRINT ‘Total orders amount is greater than 5000’
END
ELSE
BEGIN
PRINT ‘Total orders amount is less than or equals to 5000’
END
END;
In this example, we use the IF ELSE statement to get the sum of total orders and print a message according to result. The first condition checks if the total amount is greater than 5000, and if it is, the message “Total orders amount is greater than 5000″ is printed. If the first condition is not met, the ELSE block is executed, and the message “Total orders amount is less than 5000” is printed.
Nested IF ELSE Statement:
DECLARE @num INT = 15;
IF @num > 10
BEGIN
PRINT ‘The value is greater than 10.’
IF @num > 20
PRINT ‘The value is also greater than 20.’
ELSE
PRINT ‘The value is less than or equal to 20.’
END
ELSE
PRINT ‘The value is less than or equal to 10.’
In this example, we are using nested IF ELSE statements to check whether the value of the @num variable is greater than 10 and/or 20. If it is greater than 10, we print a message saying so, and then check whether it is also greater than 20. If it is, we print a message saying so, otherwise we print a message saying it is less than or equal to 20. If the value of @num is less than or equal to 10, we print a message saying so.
Updating a table based on a condition:
Suppose we want to update Total amount by 100 increment, whose total amount is less than 150.
UPDATE Orders
SET TotalAmount = TotalAmount+100
WHERE TotalAmount < 150
IF @@ROWCOUNT > 0
PRINT ‘Total amount updated for some orders’
ELSE
PRINT ‘No orders found with total amount less than 150’
In this example, we are updating the “TotalAmount” column of the Orders table for those who have Total amount Less than 150. We are then checking whether any rows were affected by the update operation using the @@ROWCOUNT system variable. If some rows were updated, we print a message saying so, otherwise we print a message saying no orders were found with total amount is less than 150.
Complex example-
Using IF ELSE to handle exceptions:
In the following example, we are using IF ELSE statements inside a TRY-CATCH block to handle exceptions.
BEGIN TRY
DECLARE @id INT = 10;
IF NOT EXISTS(SELECT * FROM Employee WHERE EmployeeId = @id)
THROW 50001, ‘Employee not found’, 1;
ELSE
PRINT ‘Employee found.’;
END TRY
BEGIN CATCH
PRINT ‘An error occurred: ‘ + ERROR_MESSAGE();
END CATCH
Output: After execution the query, we will get the below message.
In this example, we are using IF ELSE statements inside a TRY-CATCH block to handle exceptions. We are checking whether an employee with the given ID exists in the Employees table using the NOT EXISTS operator. If the employee is not found, we are throwing a custom error using the THROW statement. Otherwise, we are printing a message saying the employee was found. If any error occurs, we are catching it using the CATCH block and printing an error message.
Advantages of using IF ELSE in SQL Server:
- Improved control flow: IF ELSE statements allow you to control the flow of execution in your SQL code. You can use IF ELSE statements to branch the code based on certain conditions, making your code more flexible and powerful.
- Simplified logic: With IF ELSE statements, you can write complex logic in a simplified manner. You can use multiple IF ELSE statements to handle various scenarios, making your code more readable and maintainable.
- Error handling: IF ELSE statements can be used to handle errors in your SQL code. You can use IF ELSE statements to catch and handle errors that occur during the execution of your code.
- Implementing complex business logic: IF ELSE statements can be used to implement complex business logic in SQL code, allowing you to write code that adapts to different scenarios and requirements.
- Validating input parameters: You can use IF ELSE statements to check whether the input parameters to a stored procedure or function meet certain requirements or conditions.
Limitations of using IF ELSE in SQL Server:
- Code readability: While IF ELSE statements can simplify your code, they can also make it harder to read and understand. Complex IF ELSE statements can be difficult to follow, especially if they are nested.
- Performance overhead: IF ELSE statements can add a performance overhead to your SQL code. The more IF ELSE statements you have, the longer your code will take to execute. This can become an issue if you are dealing with large datasets or complex queries.
- Limited functionality: While IF ELSE statements are powerful, they have some limitations. They are not suitable for complex logical operations, and there are cases where other control flow statements, like CASE statements or WHILE loops, may be more appropriate.
In general, IF ELSE statements are a powerful tool for controlling the flow of execution in your SQL code. However, they should be used carefully, and with an eye toward readability and performance.
Summary:
In summary, IF ELSE statements in SQL Server allow for conditional execution of code based on certain conditions. They are used to control the flow of execution in SQL code, providing flexibility and power to developers.
Overall, IF ELSE statements are a useful tool in SQL Server for controlling code execution and implementing complex business logic.
.png)
.png)