SQL Statements › CREATE PROCEDURE › CREATE PROCEDURE Syntax and Description › Example

Example

Following is a compound statement example used in a procedure named creditLimitCheck. In the example, the compound statement begins with line number 000017 and ends with line number 000030:

 000013 -- Does the customer have enough credit to purchase an ordered part?
 000014 CREATE PROCEDURE creditLimitCheck
 000015    (INOUT result CHAR(80), IN custId CHAR(5), IN purchaseAmount DECIMAL(15,2))
 000016    LANGUAGE SQL
 000017    limitCheck: BEGIN ATOMIC
 000018       DECLARE creditAvailable DECIMAL(15,2) DEFAULT 0;
 000019
 000020       SELECT creditMax-creditUsed INTO creditAvailable
 000021              FROM credit
 000022              WHERE credit.custId =
 000023                    CAST(creditLimitCheck.custId AS NUMERIC(5));
 000024
 000025       IF (purchaseAmount > creditAvailable) THEN
 000026          SET result = 'CREDIT LIMIT EXCEEDED';
 000027       ELSE
 000028          SET result = 'CREDIT APPROVED';
 000029       END IF;
 000030    END limitCheck@

On line 00030 of the previously shown example, the at sign (@), used in conjunction with the TERM=@ parameter in DBSQLPR, enables DBSQLPR to skip over semicolons embedded in statements and instead recognize the @ as the end of the statement. For more information about TERM=, see DBSQLPR Options.