SQL Statements › CREATE PROCEDURE › SQL Procedures

SQL Procedures

SQL procedures support a basic procedural language with support for the following:

• declaration of SQL variables
• value assignment
• decision-making logic (IF-THEN statement) and CASE statement
• looping constructs (LOOP, REPEAT-UNTIL, and WHILE statements)
• error and condition handling, including user-defined conditions (see Diagnostics and Condition Handling)
• As many SQL statements as the size of your RWTSA area allows

For SQL Procedures, the CREATE PROCEDURE statement is specified as follows:

• SQL is specified for LANGUAGE in the language syntax.
• In the proc-body syntax, a proc-SQL-stmt clause is specified. The proc-SQL-stmt clause is composed of SQL Procedure statements that are coded and executed directly. For instructions about coding a list of statements and program-like logic, see the compound statement information in Compound Statement.

Following is a list of the SQL statements that support SQL Procedures:

• Assignment Statement
• CASE Statement (different from a case expression)
• Compound Statement (the building block from which SQL Procedures are mainly composed)
• condition declaration
• DATACOM DUMP Statement
• GET DIAGNOSTICS Statement
• IF-THEN Statement
• ITERATE Statement
• LEAVE Statement
• SQL variable declaration
• looping constructs
  • LOOP Statement
  • REPEAT-UNTIL Statement
  • WHILE Statement
• RAISE ERROR Statement
• RESIGNAL Statement
• SIGNAL Statement
• SIMULATE DATACOM PROCEDURE Statement (DBSQLPR only)