The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

IF, ELSE and BEGIN...END: Decide which instructions to execute

Some processes must follow different routes depending on whether information exists, a parameter is valid, or an operation has affected rows. IF evaluates a condition and decides which statement or block will be executed.

This construct controls flow: it decides which statements run inside a batch—the group sent together to SQL Server—or procedure. It does not replace CASE, which calculates a value for each row in a query.

Syntax

IF Condicion
BEGIN
    -- Instrucciones cuando la condición es verdadera.
END
ELSE
BEGIN
    -- Instrucciones cuando la condición es falsa.
END;

BEGIN…END` groups several instructions. Even if a branch has only one, preserving the block can prevent errors as the code grows.

Example: check existence

DECLARE @IdCliente INT = 3;

IF EXISTS
(
    SELECT 1
    FROM dbo.Pedido AS p
    WHERE p.IdCliente = @IdCliente
)
BEGIN
    SELECT 'El cliente tiene pedidos.' AS Resultado;
END
ELSE
BEGIN
    SELECT 'El cliente no tiene pedidos.' AS Resultado;
END;

EXISTS produces the condition. IF determines which of the two queries runs.

Validate before continuing

DECLARE @Cantidad INT = 0;

IF @Cantidad <= 0
BEGIN
    THROW 50001, 'La cantidad debe ser mayor que cero.', 1;
END;

SELECT 'La cantidad es válida.' AS Resultado;

When the error is thrown, the batch does not continue as if the value was acceptable. In a real procedure, the number and message should follow a convention known to your consumers.

Various conditions

IF @Cantidad > 0 AND @Cantidad <= 100
BEGIN
    SELECT 'Cantidad dentro del límite.' AS Resultado;
END;

Operators follow the same precedence rules as in WHERE. The parentheses help when we combine AND, OR and NOT.

IF vs. CASE

We use IF when we want to execute different blocks. We use CASE within a query when each row needs to produce a value based on a condition.

We cannot use CASE to execute an entire INSERT or SELECT arbitrarily; it is an expression, a combination that returns a value, not a control block.

What should be checked

A condition based on @@ROWCOUNT must be evaluated immediately after the instruction we want to measure. Any intermediate statement can change the value.

We also avoid long chains of IF...ELSE when a rule table, constraint, or set-based operation would better represent the problem.