Begin END

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:

123	BEGIN    { sql_statement | statement_block}END

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

123456789101112

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

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.

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:

12345678910111213141516171819

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;END

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.