Reference Manual
   CHAPTER 9. SQL Statements     

BEGIN... END statement

Function 

Groups SQL statements together.

Syntax 

[ statement-label : ]
... BEGIN [ [ NOT ] ATOMIC ]
... [ local-declaration ; ... ]
... statement-list
... [ EXCEPTION [ exception-case ... ] ]
... END [ statement-label ]

Parameters 

local-declaration:
variable-declaration
| cursor-declaration
| exception-declaration
| temporary-table-declaration

variable-declaration:
DECLARE variable-name data-type

exception-declaration:
DECLARE exception-name EXCEPTION
FOR SQLSTATE [ VALUE ] string

exception-case:
WHEN exception-name [ ,... ] THEN statement-list
| WHEN OTHERS THEN statement-list

Permissions 

None.

Side effects 

None.

See also 

DECLARE CURSOR statement

DECLARE LOCAL TEMPORARY TABLE statement

LEAVE statement

SIGNAL statement

RESIGNAL statement

Using Procedures, Triggers, and Batches

Description 

The body of a procedure or trigger is a compound statement. Compound statements can also be used in control statements within a procedure or trigger.

A compound statement allows one or more SQL statements to be grouped together and treated as a unit. A compound statement starts with the keyword BEGIN and ends with the keyword END. Immediately following the BEGIN, a compound statement can have local declarations that only exist within the compound statement. A compound statement can have a local declaration for a variable, a cursor, a temporary table, or an exception. Local declarations can be referenced by any statement in that compound statement, or in any compound statement nested within it. Local declarations are not visible to other procedures that are called from within a compound statement.

If the ending statement-label is specified, it must match the beginning statement-label. The LEAVE statement can be used to resume execution at the first statement after the compound statement. The compound statement that is the body of a procedure or trigger has an implicit label that is the same as the name of the procedure or trigger.

     For a complete description of compound statements and exception handling, see Using Procedures, Triggers, and Batches.

Standards and compatibility 

• SQL/92     Persistent Stored Module feature.

• Sybase     Supported by Adaptive Server Enterprise. This does not mean that all statements inside a compound statement are supported.

  The BEGIN and END keywords are not required in Transact-SQL.

  BEGIN and END are used in Transact-SQL to group a set of statements into a single compound statement, so that control statements such as IF ... ELSE , which only affect the performance of a single SQL statement, can affect the performance of the whole group. The ATOMIC keyword is not supported by Adaptive Server Enterprise.

  In Transact-SQL. DECLARE statements need not immediately follow a BEGIN keyword, and the cursor or variable that is declared exists for the duration of the compound statement. You should declare variables at the beginning of the compound statement for compatibility.

Example 

• The body of a procedure or trigger is a compound statement.

  CREATE PROCEDURE TopCustomer (OUT TopCompany CHAR(35), OUT TopValue INT)
  BEGIN
     DECLARE err_notfound EXCEPTION FOR
        SQLSTATE '02000' ;
     DECLARE curThisCust CURSOR FOR
        SELECT company_name, CAST(
              sum(sales_order_items.quantity *
              product.unit_price) AS INTEGER) VALUE
        FROM customer
              LEFT OUTER JOIN sales_order
              LEFT OUTER JOIN sales_order_items
              LEFT OUTER JOIN product
        GROUP BY company_name ;
     DECLARE ThisValue INT ;
     DECLARE ThisCompany CHAR(35) ;
     SET TopValue = 0 ;
     OPEN curThisCust ;
  
     CustomerLoop:
     LOOP
        FETCH NEXT curThisCust
           INTO ThisCompany, ThisValue ;
        IF SQLSTATE = err_notfound THEN
           LEAVE CustomerLoop ;
        END IF ;
        IF ThisValue > TopValue THEN
           SET TopValue = ThisValue ;
           SET TopCompany = ThisCompany ;
        END IF ;
     END LOOP CustomerLoop ;
  
  CLOSE curThisCust ;
  END