15.00 - Procedure for Creating an SQL Procedure - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Procedure for Creating an SQL Procedure

Use the following procedure to create an SQL procedure. Steps 4, 5 and 6 apply only when a compound statement is specified.

1 Identify and specify the procedure name after the keywords CREATE PROCEDURE. The procedure_name can optionally be qualified with database_name.

2 Identify the parameters, if desired, and specify them immediately after the procedure name.

The entire set of parameters is in a comma-separated list, and must be enclosed by LEFT PARENTHESIS and RIGHT PARENTHESIS characters.

Each parameter consists of 3 elements, in the following left‑to‑right order.

a Parameter type (optional)

b Parameter name (mandatory)

c Data type of the parameter (mandatory)

You cannot specify a character parameter data type with a server character set of KANJI1. Otherwise, Teradata Database aborts the request and returns an error to the requestor.

3 Specify either a single statement to perform the main task, or the BEGIN keyword for a compound statement.

If you have specified a single statement, terminate that with a final SEMICOLON character to complete the CREATE PROCEDURE statement.

If you have started to specify a compound statement, go to step 4.

4 Following the BEGIN keyword, specify any variable declarations, cursor declarations, and condition handler declarations, in that order.

Within each handler declaration, specify a single statement or a BEGIN-END compound statement as the handler action.

5 Specify the statements to perform the main tasks.

These statements must be specified after the variable, cursor, and condition handler declarations.

6 Terminate the CREATE PROCEDURE statement with a final SEMICOLON character after the END keyword.

You can specify an optional ending label for the BEGIN-END block after the END keyword. If you specify the ending label, then you must specify an equivalent beginning label suffixed with a COLON (:) character before the BEGIN keyword.