Groups SQL statements together.
[ statement-label : ] … BEGIN [ [ NOT ] ATOMIC ] … [ local-declaration ; … ] … statement-list … [ EXCEPTION [ exception-case … ] ] … END [ statement-label ]
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
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 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.
If the ending statement-label is 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 or trigger.
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.
If you specify initial-value, the variable is set to that value. If you do not specify an initial-value, the variable contains the NULL value until a SET statement assigns a different value.
If you specify initial-value, the data type must match the type defined by data-type.
For a complete description of compound statements and exception handling, see System Administration Guide: Volume 2 > Using Procedures and Batches.
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.
None