ASA SQL Reference
  SQL Statements

BEGIN statement

Description 

Use this statement to group SQL statements together.

Syntax 

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

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

Parameters 

local-declaration    Immediately following the BEGIN, a compound statement can have local declarations for objects 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.

statement-label    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.

ATOMIC    An atomic statement is a statement executed completely or not at all. For example, an UPDATE statement that updates thousands of rows might encounter an error after updating many rows. If the statement does not complete, all changes revert back to their original state. Similarly, if you specify that the BEGIN statement is atomic, the statement is executed either in its entirety or not at all.

Usage 

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.

Permissions 

None.

Side effects 

None.

See also 

DECLARE CURSOR statement [ESQL] [SP]

DECLARE LOCAL TEMPORARY TABLE statement

LEAVE statement

SIGNAL statement

RESIGNAL statement

Using Procedures, Triggers, and Batches

Atomic compound statements

Standards and compatibility 

• SQL/92    Persistent Stored Module feature.

• SQL/99    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 execution of a single SQL statement, can affect the execution 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