BEGIN … END Statement

Groups 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 
   [{ = | DEFAULT} initial-value]

initial-value:
   special-value 
   | string 
   | [ - ] number 
   | ( constant-expression ) 
   | built-in-function ( constant-expression ) 
   | NULL

special-value:
   CURRENT { 
      DATABASE 
      | DATE 
      | PUBLISHER 
      | TIME 
      | TIMESTAMP 
      | USER 
      | UTC TIMESTAMP } 
      | USER

Parameters

statement-label – if specified, it must match the beginning statement-label. You can use the LEAVE statement to resume execution at the first statement after the compound statement. The compound statement that is the body of a procedure has an implicit label that is the same as the name of the procedure.

initial-value – if specified, the variable is set to that value and the data type must match the type defined by data-type. If you do not specify an initial-value, the variable contains the NULL value until a SET statement assigns a different value.

Examples

Example 1 – the body of a procedure 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 CompanyName, CAST(
        sum(SalesOrderItems.Quantity *
        Products.UnitPrice) AS INTEGER) VALUE
    FROM Customers
        LEFT OUTER JOIN Salesorders
        LEFT OUTER JOIN SalesOrderItems
        LEFT OUTER JOIN Products
    GROUP BY CompanyName ;
  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

Usage

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

A compound statement allows one or more SQL statements to be grouped together and treated as a unit. A compound statement starts with BEGIN and ends with END. Immediately after BEGIN, a compound statement can have local declarations that exist only 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 invisible to other procedures that are called from within a compound statement.

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.

Standards

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Supported by Adaptive Server Enterprise. This does not mean that all statements inside a compound statement are supported.

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 affect the performance of only 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 BEGIN, 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.

Permissions

None