How To Use IF THEN ELSE in SQL Server?

What is the syntax of the IF THEN ELSE in SQL Server code and how to use it ?

Transact-SQL comes with rich features allowing dynamic and also robust data manipulation, and among these, the IF THEN ELSE statement serves as a pillar for decision-making within SQL Server code. This traditional coding feature enables conditional execution of T-SQL statements, creating opportunities for more dynamic and responsive queries in data manipulation.

In this SQL guide, let’s learn a few options of the IF THEN ELSE statement, using a sales table to provide real-world examples. Indeed, conditional statements are at the heart of algorithmic, which name comes from the famous Persian Mathematician Al-Khwarizmi.

What is an IF THEN ELSE statement in SQL ?

The IF THEN ELSE statement in T-SQL is an essential tool for implementing conditional logic and decision making in your SQL Server queries and applications. When it comes to implementing business rules directly in SQL queries or making stored procedures and scripts more adaptable, the IF THEN ELSE statement becomes indispensable. Essentially, using this statement effectively means writing SQL code that is adaptable and more dynamic, as it uses the value of the data itself to perform certain actions.

1. Prepare a SQL table used by the IF THEN ELSE examples

To illustrate the capabilities of the IF THEN ELSE conditional statement, let’s create and then populate the Sales table that includes a column representing the amount of each sale. Another version of the sales table was used in another tutorial, as an XML sales table. The second step of the script insert 3 sales with three different amounts.

-- Expert-Only.com
CREATE TABLE Sales (
  SalesID INT PRIMARY KEY,
  Amount  FLOAT
);

INSERT INTO Sales (SalesID, Amount)
VALUES
   (1, 200.50),
   (2, 300.75),
   (3, 150.20);

2. Basic syntax of a IF THEN ELSE T-SQL query

The basic syntax for the IF THEN ELSE statement in T-SQL appears as follows:

IF condition
    BEGIN
        -- statements to execute if condition is TRUE
    END
ELSE
    BEGIN
        -- statements to execute if condition is FALSE
    END

Note: T-SQL doesn’t actually use the THEN keyword; we’ve included it in the title for readability and to align with other programming languages that use a similar construct.

3. Simple IF ELSE SQL Server example based on a column

To begin with a straightforward example, checking if a sale exceeds a certain amount:

-- Expert-Only.com
DECLARE @SalesAmount FLOAT;
SELECT  @SalesAmount = Amount
  FROM  Sales
  WHERE SalesID = 1;

IF @SalesAmount > 200
    BEGIN
        PRINT 'This a Large Sale';
    END
ELSE
    BEGIN
        PRINT 'This a Small Sale';
    END

To expect this example to output ‘Large Sale’ because the amount for SalesID = 1 is 200.50, which is greater than 200.

4. Use ELSE IF for multiple conditions in one query

To handle multiple conditions at the same time, for example for the same column with different use cases, use the ELSE IF keyword.

DECLARE @SalesAmount FLOAT;
SELECT @SalesAmount = Amount FROM Sales WHERE SalesID = 1;

IF @SalesAmount > 300
    BEGIN
        PRINT 'Very Large Sale';
    END
ELSE IF @SalesAmount > 200
    BEGIN
        PRINT 'Large Sale';
    END
ELSE
    BEGIN
        PRINT 'Small Sale';
    END

We anticipate the output to be ‘Large Sale’ because the amount falls between 200 and 300.

5. Nested IF THEN ELSE queries in T-SQL

Nested IF THEN ELSE queries in T-SQL allow developers to handle more complex conditions by embedding additional conditional logic within existing statements. This enhances the flexibility of decision-making processes in SQL scripts, enabling the execution of specific code blocks based on intricate combinations of conditions.

DECLARE @SalesAmount FLOAT;
SELECT @SalesAmount = Amount FROM Sales WHERE SalesID = 1;

IF @SalesAmount >= 300
    BEGIN
        IF @SalesAmount >= 500
            BEGIN
                PRINT 'Extra Large Sale';
            END
        ELSE
            BEGIN
                PRINT 'Very Large Sale';
            END
    END
ELSE
    BEGIN
        PRINT 'Small or Large Sale';
    END

Conditional IF ELSE statement allows developers to create dynamic T-SQL queries

The IF THEN ELSE statement in T-SQL serves as an indispensable tool for conditional logic and decision-making within SQL Server queries and applications. To master this feature allows for writing more dynamic, efficient, and robust SQL code. Whether just starting with T-SQL or an experienced developer looking to up your game, understanding the IF THEN ELSE statement becomes crucial for writing intelligent, responsive database code.