BEGIN … END Statement

Groups SQL statements together.

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

statement-label : ]
   … BEGIN [ [ NOT ] ATOMIC ]
   … [ local-declaration ; … ]
   … statement-list
   … [ EXCEPTIONexception-case … ] ]
   … ENDstatement-label ]

local-declaration - (back to Syntax)variable-declaration 
   | cursor-declaration 
   | exception-declaration 
   | temporary-table-declaration } 

variable-declaration - (back to local-declaration)
   DECLARE variable-name [ , … ] data-type 
   [{ = | DEFAULT} initial-value]

initial-value - (back to variable-declaration)
   special-value 
   | string 
   | [ - ] number 
   | ( constant-expression ) 
   | built-in-function ( constant-expression ) 
   | NULL

special-value - (back to initial-value)
   CURRENT { 
      DATABASE 
      | DATE 
      | PUBLISHER 
      | TIME 
      | TIMESTAMP 
      | USER 
      | UTC TIMESTAMP } 
      | USER

Parameters

(back to top)

Examples

(back to top)

Usage

(back to top)

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

(back to top)

  • SQL—Vendor extension to ISO/ANSI SQL grammar.
  • SAP Sybase Database product—Supported by Adaptive Server. 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.

    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

(back to top)

None

Related reference
DECLARE LOCAL TEMPORARY TABLE Statement
DECLARE CURSOR Statement [ESQL] [SP]
LEAVE Statement
RESIGNAL Statement
SIGNAL Statement