In SQL Server, BEGIN and END are keywords used to define a statement block. They group multiple Transact-SQL statements together so that SQL Server treats them as a single unit of execution.

They are most commonly used with control-flow logic like IF...ELSE statements, WHILE loops, and within Stored Procedures or Triggers.

1. Basic Syntax

A block starts with BEGIN and finishes with END. Any code placed between them is executed together.

BEGIN
    -- Statement 1
    -- Statement 2
    -- ...
END

2. When to Use BEGIN…END

Use with IF…ELSE

By default, an IF statement only controls the one immediate statement following it. If you want to execute multiple lines based on a condition, you must wrap them in a BEGIN...END block.

Example:

DECLARE @SalesCount INT = 55;

IF @SalesCount > 50
BEGIN
    PRINT 'Target reached!';
    UPDATE SalesSummary SET Status = 'Bonus Eligible';
END
ELSE
BEGIN
    PRINT 'Target not reached.';
    UPDATE SalesSummary SET Status = 'Standard';
END

Use with WHILE Loops

Similar to IF, a WHILE loop only repeats the single next statement unless a block is used.

Example:

DECLARE @Counter INT = 1;

WHILE @Counter <= 3
BEGIN
    PRINT 'Iteration Number: ' + CAST(@Counter AS VARCHAR);
    SET @Counter = @Counter + 1;
END

3. Nesting Blocks

You can place BEGIN...END blocks inside other blocks. This is useful for complex logic where you have multiple layers of conditions.

Example:

BEGIN
    PRINT 'Starting Batch...';
    
    IF (SELECT COUNT(*) FROM Products) > 0
    BEGIN
        PRINT 'Products found, checking stock...';
        
        IF (SELECT SUM(UnitsInStock) FROM Products) < 10
        BEGIN
            PRINT 'Warning: Stock is low!';
        END
    END
    
    PRINT 'Batch Complete.';
END

4. Usage in Stored Procedures and Triggers

When you define a Stored Procedure, the AS keyword is usually followed by a BEGIN...END block. While not strictly required for procedures with only one statement, it is best practice to always use them for clarity and to avoid errors when adding code later.

CREATE PROCEDURE UpdateUserStatus
    @UserID INT
AS
BEGIN
    SET NOCOUNT ON; -- Prevents extra "rows affected" messages
    
    UPDATE Users 
    SET LastLogin = GETDATE() 
    WHERE ID = @UserID;
    
    PRINT 'User status updated.';
END

5. Practical Use in Stored Procedures

While not strictly required for the entire body of a stored procedure (the AS keyword handles that), many developers use them to clearly define the start and end of the procedure logic.

CREATE PROCEDURE UpdatePrice
    @ID INT,
    @NewPrice MONEY
AS
BEGIN
    -- This block clearly defines the procedure body
    UPDATE Products SET Price = @NewPrice WHERE ProductID = @ID;
    SELECT @@ROWCOUNT AS RowsAffected;
END

Categorized in:

Stored Procedures,