Mastering IF THEN Logic in SQL Server

In the world of database management, having a firm grasp of T-SQL control-of-flow statements is a crucial skill. These statements are commands that determine how the execution of Transact-SQL (T-SQL) code proceeds based on certain conditions.  

As a database developer, DBA, or data analyst, you can use the T-SQL control-of-flow statements to dynamically manage the flow of program execution and make complex logic implementation possible. A good example of the T-SQL control-of-flow statements is the IF…ELSE command. You can use this statement to direct the database engine to run one block of code if a condition is true and another if it is false.  

In this article, you’ll learn the fundamentals and advanced usage of IF-THEN logic in SQL Server. We’ll walk through syntax examples, highlight common pitfalls, and share best practices. You’ll also see how dbForge Studio for SQL Server simplifies working with conditional logic and enhances productivity. 

Table of contents

• Understanding T-SQL control-of-flow statement
• Understanding SQL Server IF statements
• SQL Server IF-THEN statements
• IIF function in SQL Server
• Advanced usage of IF-THEN
• Conclusion

Understanding T-SQL control-of-flow statement

What are T-SQL control-of-flow statements? 

T-SQL control-of-flow statements are commands used to manage the order in which SQL statements are executed. These statements enable you to incorporate decision-making, branching, and looping into your queries and stored procedures, allowing you to handle complex logic and dynamic conditions. 

Some of the most common control-of-flow statements in SQL Server include: 

• IF...THEN: executes a block of code when a specified condition evaluates to true. If the condition is false, no alternative action is taken.
• IF...ELSE: extends the IF statement by allowing an alternative block of code to run when the condition is false.
• WHILE: repeats a block of code as long as a condition remains true, making it useful for iterative tasks.
• CASE: provides conditional branching within a query, allowing different outcomes based on multiple conditions or values.
• BEGIN...END: groups multiple statements into a single logical unit, often used alongside other control-of-flow commands.
• GOTO: transfers control to a labelled section of code, enabling direct jumps within a script or stored procedure.
• TRY...CATCH: implements structured error handling, letting you run error-prone code in the TRY block and handle failures in the CATCH block.
• BREAK: exits the nearest enclosing loop, often used to stop WHILE loops based on a condition.
• CONTINUE: skips the current loop iteration and proceeds to the next, useful for bypassing certain conditions inside loops.

By combining these statements effectively, you can build flexible and powerful SQL scripts that handle conditional logic, control execution flow, and adapt to different business requirements.

Understanding SQL Server IF statements

To execute conditional logic in SQL Server, understanding how the IF statement works is essential. The syntax for this statement is straightforward: 

IF condition 
    BEGIN 
        -- code block to execute if the condition is true 
    END; 

The condition in this syntax is an expression that evaluates to either true or false. If the condition is true, the code block enclosed within BEGIN…END will run; otherwise, it will be skipped. 

For example: 

DECLARE @value INT = 10; 
 
IF @value > 5 
    BEGIN 
        PRINT 'The value is greater than 5.'; 
    END; 

In this example, the variable @value is set to 10. The IF statement checks whether it is greater than 5. Since the condition evaluates to true, SQL Server prints the message “The value is greater than 5.” When executed in dbForge Studio for SQL Server, the output appears in the Messages tab of the Error List. 

SQL Server IF-THEN statements

How to use IF-THEN in SQL Server 

Use the IF statement in SQL Server to evaluate a condition and execute code if it’s true. This action allows you to handle conditional logic directly within your scripts or stored procedures. 

For example, imagine a Product table in a bicycle store database. The following query checks if there are items with a price greater than a given threshold and prints a custom message: 

DECLARE @Threshold DECIMAL(10, 2) = 1000; 
 
IF EXISTS ( 
    SELECT * 
    FROM Production.Product 
    WHERE Price > @Threshold 
) 
BEGIN 
    PRINT 'There are products with prices greater than $' + CAST(@Threshold AS VARCHAR); 
END; 

If any product costs more than $1000, SQL Server prints: 
“There are products with prices greater than $1000.” 

Otherwise, nothing happens because no alternative action is defined. 

How to use IF-ELSE in SQL Server? 

To handle both outcomes of a condition—whether it is true or false—extend the logic with an IF…ELSE statement. Its syntax is: 

IF condition 
    BEGIN 
        -- code block to execute if condition is true 
    END 
ELSE 
    BEGIN 
        -- code block to execute if condition is false 
    END; 

Let’s update the previous example with a higher threshold that intentionally returns false: 

DECLARE @Threshold DECIMAL(10, 2) = 10000; 
IF EXISTS ( 
    SELECT * 
    FROM Production.Product 
    WHERE Price > @Threshold 
) 
BEGIN 
    PRINT 'There are products with prices greater than $' + CAST(@Threshold AS VARCHAR); 
END 
ELSE 
BEGIN 
   PRINT 'No products with prices greater than $' + CAST(@Threshold AS VARCHAR); 
END; 

Now, whether the condition is true, SQL Server returns a message: 

• If products exceed the threshold, it prints the “greater than” message.
• If not, it prints the “No products” message. 

When to use IF-THEN vs. IF-ELSE 

• Use IF-THEN when you only need to act on a true condition.
• Use IF-ELSE when you want explicit behavior for both true and false outcomes. 

These constructs make your SQL scripts more robust and adaptable, especially for decision-based workflows like inventory checks, business rules, or conditional data processing. 

IIF function in SQL Server

The IIF function provides a shortcut for IF-THEN-ELSE logic in SQL Server expressions. It acts as an inline condition or ternary logic operator, returning a value directly based on whether a condition evaluates to true or false. Unlike the IF statement, which controls program flow in procedural code, IIF is a scalar function that can be used inside queries, SELECT clauses, or computed columns. 

The syntax is straightforward: 

IIF(condition, true_value, false_value); 

• Condition: this implies the expression to evaluate.
• True_value: the value returned when the condition is true.
• False_value: the value returned when the condition is false. 

Examples of IIF function 

You can use the IIF function to simplify conditional logic in SQL Server. Here are examples of how it works in practice. 

Example: display in-stock status 

SELECT  
  ProductId, 
  IIF(Qty > 0, 'Yes', 'No') AS in_stock 
FROM Production.Stock; 

This query checks the Qty value for each product. 

• If the quantity is greater than 0, then the in_stock column shows Yes.
• If the quantity is 0, the column shows No. 

Expected output: a result set with product IDs and a clear “Yes/No” availability indicator. 

Example: calculate discounted price 

SELECT 
  Name, 
  Price, 
  IIF(Discount > 0, Price - Discount, Price) AS discounted_price 
FROM Production.Product; 

Here, the query adds a discounted_price column:

• If a discount exists, it subtracts the discount from the full price.
• If no discount, the original price is returned. 

Expected output: a table listing each product with its name, full price, and the correctly calculated discounted price. 

Why use IIF? 

The IIF function is especially useful when you want to achieve any of the following: 

• You need concise conditional expressions inside a SELECT statement.
• You want to avoid verbose IF…ELSE blocks in procedural code.
• You’re working with computed columns, filters, or inline logic that depend on quick true/false evaluation. 

By condensing conditional logic into a single function call, IIF improves both readability and efficiency of SQL queries. 

Advanced usage of IF-THEN

What are advanced ways to use IF-THEN in SQL Server? 

Beyond the basic scenarios and examples we have considered, IF-THEN statements can be applied in more sophisticated ways to manage complex business rules and maintain data integrity. Advanced usage often involves: 

• Nested logic, where multiple conditions are evaluated in sequence.
• Transaction control, where conditional checks influence data updates and commits.
• Error handling and validation, ensuring reliable query execution. 

In the following examples, we’ll explore: 

1. How to nest IF-THEN statements for layered decision-making.

2. How to combine IF-THEN with transactions to manage data modifications safely.

3. Common pitfalls and best practices for writing maintainable conditional logic. 

Example 1: Nested IF-THEN statements 

You can nest IF-THEN statements to check multiple conditions and run specific code blocks accordingly. This is useful for scenarios where different thresholds or rules apply. 

IF EXISTS ( 
    SELECT * 
    FROM Sales.Orders 
    WHERE TotalAmount > 1000 
) 
BEGIN 
  -- Outer IF-THEN block 
  PRINT 'High-value orders found.'; 
 
  IF EXISTS ( 
      SELECT * 
      FROM Sales.Orders 
      WHERE TotalAmount > 5000 
  ) 
  BEGIN 
    -- Inner IF-THEN block 
    PRINT 'There are orders with a total amount exceeding $5000.'; 
  END 
  ELSE 
  BEGIN 
    -- Inner IF-THEN alternative block 
    PRINT 'No orders with a total amount exceeding $5000 found.'; 
  END 
END 
ELSE 
BEGIN 
  -- Outer IF-THEN alternative block 
  PRINT 'No high-value orders found.'; 
END; 

In this script: 

• The outer condition checks for orders over $1000.
• The inner condition further checks for orders exceeding $5000.
• Each branch provides clear output depending on the results. 

Example 2: Using IF-THEN with transactions 

You can also use IF-THEN statements to influence transactions, ensuring that data changes align with business rules. 

BEGIN TRANSACTION; 
 
DECLARE @OrderId INT; 
DECLARE @TotalAmount DECIMAL(10, 2); 
DECLARE @PaymentStatus VARCHAR(20); 
 
-- Determine payment status based on order value 
IF @TotalAmount > 1000 
BEGIN 
  SET @PaymentStatus = 'Pending'; 
END 
ELSE 
BEGIN 
  SET @PaymentStatus = 'Approved'; 
END; 
 
-- Update the order record 
UPDATE BicycleStoreDemo.Sales.Orders 
SET PaymentStatus = @PaymentStatus 
WHERE OrderId = @OrderId; 
 
-- Commit the transaction 
COMMIT; 
 
-- Confirm result 
IF @PaymentStatus = 'Pending' 
BEGIN 
  PRINT 'Payment for the high-value order has been set to Pending.'; 
END 
ELSE 
BEGIN 
  PRINT 'Payment for the order has been successfully processed.'; 
END; 

Here’s what happens: 

• Orders above $1000 are flagged as Pending.
• Orders below $1000 are marked as Approved.
• The transaction ensures that updates are consistent and safely committed.

Common pitfalls and best practices

When working with advanced IF-THEN logic, keep the following in mind: 

• Be mindful of the logical order of conditions in nested statements.
• Avoid excessive nesting, which reduces readability and maintainability.
• Use consistent indentation, formatting, and comments for clarity.
• Incorporate error handling and transaction management when modifying data.
• Regularly refactor code to simplify complex logic and improve performance.
• Test with different scenarios, including edge cases, to validate correctness. 

By applying these practices, you’ll make your IF-THEN logic not only robust but also maintainable and performance-friendly.

Conclusion

In summary, SQL Server offers multiple ways to handle conditional logic, including IF, IIF, and control-of-flow statements like WHILE or CASE. The IF statement is the foundation, allowing you to run code when a condition evaluates to true, while IF…ELSE extends this by defining an alternative path when the condition is false. The IIF function provides a concise, inline shortcut for IF-THEN-ELSE logic, making queries more compact and readable. For more complex scenarios, advanced IF-THEN usage, such as nested conditions or combining logic with transactions, helps enforce business rules and maintain data integrity. 

By following best practices like avoiding excessive nesting, formatting code for clarity, and handling transactions safely, you can ensure that your T-SQL logic is both reliable and maintainable. To explore these techniques in practice, download a 30-day free trial version of dbForge Studio for SQL Server. This powerful tool offers the best environment for writing, testing, and optimizing conditional logic in your SQL scripts.

If you have been looking for a comprehensive guide on the MySQL IF statement, you will be delighted to find it on our blog.