Procedure for Creating an SQL Procedure | CREATE PROCEDURE | Teradata Vantage - 17.10 - Procedure for Creating an SQL Procedure - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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.
    • Parameter type (optional)
    • Parameter name (mandatory)
    • Data type of the parameter (mandatory)

      You cannot specify a character parameter data type with a server character set of KANJI1. Otherwise, the 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.