Conditional Structures CASE and IF...ELSE

In T-SQL, conditional control structures are instructions that allow a database engine to evaluate logical expressions to decide which block of code to execute or which value to return.

So far, our scripts have been sequential: line 1, line 2, line 3… But real business logic needs branches: “If it’s the end of the month, calculate payroll; if not, do nothing” or “If the customer is VIP, apply a discount”.

In T-SQL, we have two main tools for making decisions, but they are used in very different contexts: CASE and IF…ELSE.

CASE (Conditional logic in data)

The IF is used to control which statements are executed (program flow). The CASE is used to generate a value based on conditions within a SELECT, UPDATE, or DELETE query.

There are two formats:

SELECT Nombre,
    CASE CategoriaID
        WHEN 1 THEN 'Electrónica'
        WHEN 2 THEN 'Ropa'
        WHEN 3 THEN 'Juguetes'
        ELSE 'Otros' -- Default value if none match
    END AS NombreCategoria
FROM Productos;

SELECT Nombre, Precio,
    CASE
        WHEN Precio < 10 THEN 'Barato'
        WHEN Precio BETWEEN 10 AND 50 THEN 'Estándar'
        WHEN Precio > 50 THEN 'Premium'
        ELSE 'Sin Clasificar'
    END AS RangoPrecio
FROM Productos;

IF…ELSE (Flow control)

The IF structure evaluates a logical condition (true or false). If true, it executes the following code. If false (and an ELSE exists), it executes the alternative code.

The syntax is as follows,

DECLARE @Stock INT = 5;

IF @Stock < 10
BEGIN
    PRINT 'El stock es bajo';
    PRINT 'Solicitando reposición...';
    -- You could put an INSERT INTO Pedidos... here
END
ELSE
BEGIN
    PRINT 'Stock suficiente';
END

DECLARE @Stock INT = 5;

-- ⛔ COMMON ERROR: Forgetting BEGIN/END
IF @Stock < 10
    PRINT 'El stock es bajo';
    PRINT 'Solicitando reposición...'; -- This line ALWAYS executes!

IF @Stock < 10
BEGIN
    IF @Stock = 0
        PRINT '¡Urgente! Rotura de stock';
    ELSE
        PRINT 'Stock bajo, pero aún queda';
END

The IIF function

Since SQL Server 2012, we have a function called IIF (Immediate IF), which is a conditional ternary operator in SQL. (basically a shorthand way of writing a simple CASE)

The syntax is,

IIF(condition, value_if_true, value_if_false)

Or seen in an example,

SELECT
    Nombre,
    IIF(Stock > 0, 'Disponible', 'Agotado') AS Estado
FROM Productos;