SQL Server BEGIN END

Summary: in this tutorial, you will learn how to use the BEGIN...END statement to wrap a set of Transact-SQL statements into a statement block.

Overview of the BEGIN...END statement

The BEGIN...END statement is used to define a statement block. A statement block consists of a set of SQL statements that execute together. A statement block is also known as a batch.

In other words, if statements are sentences, the BEGIN...END statement allows you to define paragraphs.

The following illustrates the syntax of the BEGIN...END statement:

BEGIN
    { sql_statement | statement_block}
END
Code language: SQL (Structured Query Language) (sql)

In this syntax, you place a set of SQL statements between the BEGIN and END keywords, for example:

BEGIN
    SELECT
        product_id,
        product_name
    FROM
        production.products
    WHERE
        list_price > 100000;

    IF @@ROWCOUNT = 0
        PRINT 'No product with price greater than 100000 found';
END
Code language: SQL (Structured Query Language) (sql)

Output:

To view the messages generated by the PRINT statement, in SQL Server Management Studio, you need to click the Messages tab. By default, the Messages tab is hidden.

In this example:

• First, we have a block starting with the BEGIN keyword and ending with the END
  keyword.
• Second, inside the block, we have a SELECT statement that finds products whose list prices are greater than 100,000. Then, we have the IF statement to check if the query returns any product and print out a message if no product returns.

Note that the @@ROWCOUNT is a system variable that returns the number of rows affected by the last previous statement.

The BEGIN... END statement bounds a logical block of SQL statements. We often use the BEGIN...END at the start and end of a stored procedure and function. But it is not strictly necessary.

However, the BEGIN...END is required for the IF ELSE statements, WHILE statements, etc., where you need to wrap multiple statements.

Nesting BEGIN... END

The statement block can be nested. It simply means that you can place a BEGIN...END statement within another BEGIN... END statement.

Consider the following example:

BEGIN
    DECLARE @name VARCHAR(MAX);

    SELECT TOP 1
        @name = product_name
    FROM
        production.products
    ORDER BY
        list_price DESC;
    
    IF @@ROWCOUNT <> 0
    BEGIN
        PRINT 'The most expensive product is ' + @name
    END
    ELSE
    BEGIN
        PRINT 'No product found';
    END;
ENDCode language: SQL (Structured Query Language) (sql)

In this example, we used the BEGIN...END statement to wrap the whole statement block. Inside this block, we also used the BEGIN...END for the IF...ELSE statement.

In this tutorial, you have learned about SQL Server BEGIN...END statement to wrap Transact-SQL statements into blocks.