Establishing Variables and Data Values Teradata

A stored procedure can be written to provide additional functionality by naming local variables to store and manipulate values. The variables receive their values either by being explicitly assigned internally or passed from the client that calls the procedure. A SET statement is used to assign a value to a variable within a procedure. The value may be a literal or an equation. However, before using a variable name, the name and the data type must be defined. A DECLARE statement is used to establish the variable. Both of these commands are covered next.

DECLARE Statement to Define Variables

The DECLARE is primarily used to establish a local variable name for use within the stored procedure. The variable is called a local because the name is only available within the procedure.

The syntax for defining one or more variables is:

DECLARE <variable-name-list> <data-type> [ DEFAULT <initial-value> ] ;

The variable name list may contain one or more variable names. However, only a single data type may be defined within an individual DECLARE statement. So, if three variables are needed and each one is a different data type, three DECLARE statements are required. However, if all three variables are of the same data type, only a single DECLARE is needed. At the same time, it is permissible to use one DECLARE for each variable regardless of its data type. This might be the case when all three variables need to start with an different initial value in each one using the DEFAULT.

Two variables in the same procedure may not have the same name. Names must be unique within procedures just as they must be unique within a macro or database. Additionally, a variable name cannot be any of these reserved Status variable names:


The data type declared must be valid within Teradata, like in a CREATE TABLE request.

Optionally, a DEFAULT value may be specified. When this is done, every time the procedure is executed, the variable is automatically be set to the value specified. Otherwise, it will contain a NULL value. The DEFAULT value, if specified, applies to all variables specified in a single DECLARE statement.

The scope or validity of a local variable is the BEGIN/END block. Access to the value in a local variable is not directly available from outside of the procedure. A local variable may be used within SQL. When it is specified in an SQL statement, it must be prefixed with a colon (:). The colon indicates that substitution is to occur. At execution time, the value stored in the variable is substituted into the SQL in place of the name. Then, the SQL statement is executed.

The next procedure defines var1 as a local variable and assigns an initial value using a DEFAULT and then substitutes the value into the WHERE clause of a DELETE:

This example is only a building block approach to writing stored procedures. This procedure is not flexible enough to delete any other row from the table. Therefore, it is a single use procedure. A procedure is most beneficial when it is used multiple times by multiple users. By incorporating other SPL and SQL, it will evolve into a useful procedure.

SET to Assign a Data Value as a Variable

The SET statement is an assignment statement. Once the variable name and data type have been defined, a value needs to be assigned. As seen above, the DEFAULT can establish an initial value. Otherwise, a value can be assigned or SET within the procedure. Then, it may retain the initial value throughout the life of the procedure, or may be modified at any time using a SET command.

The syntax for the SET statement follows:

SET <assignment-target> = <assignment-source> ;

The assignment target can be either a local variable or a parameter. Parameters are covered later. The assignment source can be any of the following: a literal value, a local variable, a status variable, a parameter, an SQL CASE expression, a cursor, a column, an alias name, or a mathematical expression.

The following is an alternate Second_Procedure used to SET the value instead of using DEFAULT:

Even the SET by itself is still limited, the next provides the best technique for assigning data values to a procedure with values passed as parameters.

Status Variables

These status variables are provided within the procedure body to determine the outcome of operations requested:

SET to Assign a Data Value as a Variable

A successful completion will assign the value of zero to each of the SQLSTATE and SQLCODE variables. Since SQLSTATE is a CHAR field, it will be ‘00000’ for comparison purposes. Other values should be referenced in the V2R4 Reference manual to determine what occurred for debug or correction purposes.

There is another reserved name for use in determining that an SQL error occurred. Its name is SQLEXCEPTION and is handy in checking for any non-zero outcome. It becomes the unknown or unspecified outcome, but is still considered an error condition. Checking for one or more specific outcomes is the purpose of SQLSTATE.

Again, these are all "read only" variables and cannot be used in the SET assignment statement as the assignment target.

Assigning a Data Value as a Parameter

Like macros, stored procedures may receive information passed from the client software. This ability provides much more flexibility and enhances the power of stored procedures.

Unlike macros, a stored procedure can return a parameter value to the calling user or routine. A macro can only return the output rows from a SELECT or status code to indicate the success or failure of the macro. This parameter output capability allows the stored procedure a way to offer functionality different than a macro. Depending on the functionality needed, either a macro or a stored procedure might be written.

When using parameters in a stored procedure, the syntax for a parameter list is comprised of these three elements:

<parameter-usage> <parameter-name> <data-type>

Where each element is defined as:

  • <parameter-usage> can be one of these:
    • IN only provides input from the caller (default)
    • OUT only provides output to the caller
    • INOUT allows both input and output for the caller
  • <parameter-name> is any valid Teradata SQL name
  • <data-type> is any valid Teradata data type

The following is an alternative to and a better version of Second_Procedure that passes the value for var1 instead of using a DECLARE or SET:

The value of var1 dynamically comes from the client at execution time. It is no longer fixed by the DECLARE or the SET commands. The flexibility now exists within Second_Procedure to delete any row from the table. Furthermore, there is no requirement to change the procedure every time a different row need s to be deleted. The CALL statement for this procedure appears in the next section along with a discussion on its use and the passing of parameter values.

Considerations for parameter definition:

  • A Parameter is valid throughout the entire stored procedure
  • If a parameter is referenced within an SQL statement, it must be prefixed with a colon (:) to tell the optimizer that it is a variable name and not an object name
  • A Parameter name cannot be any of these names since they are reserved for status variable names:
  • The DEFAULT clause cannot be specified for parameters to establish an initial value
  • IN parameters cannot be part of the assignment target for any SPL or SQL statement
  • OUT parameters cannot be part of the assignment source for any SPL or SQL statement

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics