Additional SPL Statements Teradata

This section of the book discusses and demonstrates the SPL commands allowed within the body of the stored procedure. The first thing to notice is that they are not covered below in alphabetic sequence; like the list above.

We struggled with this departure. However, it is done in this manner to gradually build up to the more involved capabilities of the commands, like using a FOR loop.

CALL Statement

The CALL is used to execute a stored procedure. The CALL statement is normally executed either by a client program or interactively by a user. Additionally, a stored procedure is allowed to call another stored procedure.

The syntax to CALL a stored procedure:

CALL [<database-name>.]<stored-procedure-name> ( [<parameter-list> ] ) ;

The procedure name can be qualified with a database name to guarantee the correct procedure is used. A parameter list is only used when the procedure is expecting IN parameter values passed to it. The list must match the exact number of parameters in the procedure. Remember, even if you are not passing parameter values in the parameter list, you must still code the ( ) open parenthesis and close parenthesis to call the procedure.

The following CALL executes the first procedure shown above:

CALL First_Procedure ( );

Since the First_Procedure does not define any parameters, none are passed to it. However, Second_Procedure has evolved to the point of expecting a parameter the value at run time to identify the row to delete. The next CALL executes the Second_Procedure and passes the value of 11111111:

CALL Second_Procedure(11111111);

Now, the procedure can be called again and a different row can be deleted using:

CALL Second_Procedure(31313131);

This is where a stored procedure is most beneficial. One procedure performs an operation that is needed more than once. The best situation for creating a stored procedure exists when the execution of the same operation is needed more than once and by more than one user.

Using a procedure makes the operation very consistent because the statements are not rewritten; they are stored in Teradata. It is very efficient because the statements are not transmitted across the network; they are stored. It is very easy because the only request is to call the procedure.

Considerations when executing the CALL:

  • User must have EXECUTE PROCEDURE privilege on the stored procedure object or on the containing database
  • Cannot be specified in a multi-statement transaction
  • Number of arguments must match the number of parameters in the stored procedure
  • A value expression as a call argument must not reference tables
  • Can be the only statement in a MACRO
  • Can return at most, one row response
  • If a value expression is used as a call argument (colon preceded identifiers) must refer to variables associated with a USING clause for the requester issuing the CALL
  • TITLE, FORMAT, and NAMED phrases can be specified with the call arguments via an SQL CAST function
  • An IN and INOUT argument must be a value expression
  • An OUT argument must have a placeholder (OUT parameter name)
  • The values placed into the INOUT and OUT parameters are returned to client
  • A value expression of NULL can be used to initialize one or more parameters to a NULL

IF / END IF Statement

An IF statement can be used within a procedure to evaluate the result of a comparison. It can check the outcome of an operation or compare the contents of a variable. It has a THEN portion for a TRUE comparison and an optional ELSE when FALSE. Additionally, there is an optional ELSEIF to nest or imbed another comparison within the IF processing. The IF statement is delineated by an END used to specify the end of the conditional processing.

The syntax of the IF statement follows:

The next example performs three IF statement tests with literals to compare the values of the two input parameters:

Although this works, it is not the most efficient way to code it. The above procedure performs all three tests, even if the first test is TRUE. This is due to the execution sequence from top to bottom. Therefore, one of the tests will always "pass" with a TRUE and the other two will always "fail" with a FALSE result, regardless of the values passed.

The following example uses the IF statement to make the same tests as above, but it is more efficient using the ELSEIF and ELSE portions in a single IF, not three:

When called with two values for var1 and var2, the procedure tests the values using the IF for all 3 possibilities: equal, less than and greater than. When the equal test condition is found to be TRUE, the THEN portion is used and the comparison is complete. If the variables are not equal, it then does the ELSEIF test for var1 being less than var2. If this test is TRUE, it does the second SET in the THEN and now it is complete. However, if both of these tests fail, the final ELSE is used to set a value for the message variable to indicate that var1 is less than var2.

The above example also demonstrates the usage of OUT for a parameter. The value placed into the Msg variable will be returned to the calling client program. Using OUT and INOUT is the only way for a procedure to return data to a client.

The next CALL statement demonstrates the use of Test_Proc by passing equal values to it and reserves an area for the output message (msg):

CALL Test_Proc(1,1,msg);

Msg _

They are equal

The following CALL statement demonstrates the use of Test_Proc using the smaller number first:

CALL Test_Proc (1,2,msg);

Msg _

Variable 1 less

The next CALL statement demonstrates the use of Test_Proc using the smaller number second:

CALL Test_Proc (2,1,msg);

Msg _

Variable 1 greater

The client is responsible for providing the correct number of parameters to the CALL and in the correct order. Since Queryman is used for these examples, the name of the OUT parameter must be used in the CALL to properly connect the two statements (within the CALL and the CREATE PROCEDURE).

LOOP / END LOOP Statements

The LOOP and END LOOP define an unconditional loop. This means that the SQL and SPL contained in the logic loops or repeats indefinitely. Since looping indefinitely is not a good thing, there must be a way to stop the loop when the processing is completed.

The syntax for the LOOP / END LOOP follows:

[<label name>:]LOOP<statement-list>; END LOOP [ <label name> ] ;

The label name is optional unless putting a loop within a loop. However, when the label name appears on the LOOP statement, it must also appear on the END LOOP to connect them together and define the statements to repeat.

The statement list in this syntax format contains SPL and DML statements.

Since writing an indefinite loop is probably a bad thing to do, an example does not appear here. Instead, the LOOP example is combined with the LEAVE command, covered next.

LEAVE Statement

The LEAVE is primarily used in conjunction with a looping or repeating set of logic based on one of the looping SPL statements. The LEAVE provides a way to exit or leave the loop. Therefore, it will only be coded within a BEGIN, FOR, LOOP or WHILE section of repeating logic.

The syntax for LEAVE follows:

LEAVE [ label name> ] ;

The following combines the LOOP and LEAVE statements to establish a conditional loop:

The above procedure called Inserter_Five incorporates many of the previously discussed SPL commands. It shows all of the pieces needed to process and control a loop. First, the DECLARE establishes a variable called Cntr and sets its initial value to 0 using the DEFAULT option. Second, it enters the loop and increments Cntr by adding 1 on each repetition of the loop. Next, it tests the value in Cntr to see if it is greater than 5. When it is, the loop is exited. Otherwise, the loop performs the INSERT to store a row with the values of Cntr and the current time as columns into the My_Log_Tbl table.

The code allows the loop to repeat and INSERT 5 rows. This is because as soon as the IF test determines that Cntr has reached 6 (1 greater than 5 after the last addition), the THEN performs the LEAVE statement to exit the loop. The LEAVE prevents the INSERT from being performed indefinitely.


The WHILE and END WHILE are used to establish a conditional loop. As long as the condition is TRUE, the loop of logic continues. Once the condition is no longer TRUE, the logic is exited.

As seen above, when using the LOOP statement, the IF and LEAVE statements can be used to control the loop by adding a conditional test. The conditional test is built into the WHILE statement and tested each time the loop begins to repeat. So, at the top of the loop, when the test is TRUE the loop logic is performed. However, when the test fails at the top of the loop, the logic is skipped and not performed.

The syntax for the WHILE / END WHILE follows:

The statement list contains SPL and DML statements.

The label name is optional unless putting a loop within a loop. When the label name appears on the WHILE, it must also appear on the END WHILE.

The following uses the WHILE statement instead of the LOOP and LEAVE to perform the exact same conditional loop processing seen above:

The WHILE statement above tests Cntr for a value less than 5. When it is, the loop is executed. The LOOP and LEAVE example used an IF to test for Cntr greater than 5. Remember, the WHILE tests before the loop and the IF tests within the loop. Both of these examples inserted five rows into the log table.

FOR / END FOR Statements

The FOR and END FOR statements may also be used to perform loop control. However, its operation is considerably different from both LOOP and WHILE. It is the most involved and requires some additional logic and names that must be established when writing a procedure with it. Therefore, it is being presented after the other looping techniques.

When using SQL within a procedure, it is restricted to accessing one row only. This means that the SQL must use a WHERE clause and reference a column that is defined as either a UPI or a USI index. However, when using a FOR loop multiple rows are allowed to be accessed. The reason for this distinction is that a single row does not use spool space. The results are returned directly to the client. Multiple rows must use spool space and spool must be managed.

In order for a client to retrieve more than one row from spool, it is the responsibility of the client to manage the spooled rows and request each row individually when desired, not all rows at once. The cursor indicates to the database which row to return from spool; again, one row at a time. If you have use cursor, are you required to pay a syntax (sin tax)? OK, we'll get back to being serious.

A brief discussion on cursors is warranted for anyone not familiar with the Call Level Interface (CLI) or the SQL Preprocessor. Any program that interfaces with a database must request each individual row from the database. Once the rows are returned as records and the columns as fields, the client program can process the data directly or present it to the user for display. This is true for BTEQ as well as Queryman clients. BTEQ builds a report format and Queryman builds a spreadsheet format. Either way, the client software is responsible for building the output format, not Teradata.

The client program performs a standard DECLARE CURSOR command. The program is also responsible to FETCH each row based on the cursor location (row pointer). The standard FETCH command must tell the database (Teradata) which row to retrieve from spool. Therefore, the FETCH must be in a loop and the cursor must be incremented on each repetition of the loop to retrieve the next row.

When using SQL within BTEQ or Queryman, they manage the return of no rows, one row or more than one row. All the user needs to do is provide the DML statement(s). They use a FETCH command to tell the database to send a row from spool. A stored procedure may be called from both of these clients, but rows are not returned to the clients. Remember, stored procedures do not return rows to the user. It is now the responsibility of the stored procedure to FETCH the rows. The FETCH operation requires a cursor to identify the next row to return.

Stored procedures must use this same technique. However, the user is not responsible to code the actual CLI commands. Instead, the FOR and END FOR tell the procedure to request a cursor and loop through the logic with a FETCH occurring automatically. It makes our life easier – which is a good thing!

The syntax for the FOR and END FOR is below:

The label name on the FOR is optional. However, if it is used on the FOR, it must also be used on the END FOR statement for identification.

The FOR loop variable is used to establish a name to identify the current row. The row is pointed to using the value stored in the cursor. The writer of the procedure uses the cursor name to fully qualify column references in the procedure.

The cursor name may be used in the WHERE clause or in the SQL to indicate the current row in spool. It is an alias name for the FOR loop pointer variable. The name used in the qualification is the name of the cursor declared in the cursor specification of the FOR statement.

The cursor specification consists of a SELECT statement that returns at least one column from one or more rows. Like creating views and derived tables, all calculations and literals in the SELECT list must have an alias to constitute a valid name.

The statement list is one or more SQL or SPL statements used to further process the current row from the cursor specification. If the statement list contains a positioned UPDATE or DELETE statement, the cursor is treated as "updateable" (allowed only in ANSI mode).

An updateable cursor allows for the value in the cursor to be changed by the user. In other words, if the cursor were pointing at row 12, the user could set it back to 1 and reprocess the same rows again. Otherwise, the cursor will only increment from the first row to the last row, one row at a time.

The statement list cannot contain a transactional control statement if the cursor is updateable.

Examples of transactional control statements:

  • COMMIT for ANSI Mode
  • BEGIN TRANSACTION for Teradata Mode

The next procedure uses a cursor to INSERT rows into a LogTbl after the SELECT retrieves them from My_Table:

There are several things to note when evaluating the processing of the FOR command in a procedure. First, the FOR names the cursor and an alias for the cursor. These names are available for reference within the SQL appearing after the DO portion of the FOR. Second, the SELECT in the CURSOR FOR is part of the FOR and does not have a semicolon. Lastly, the cursor named cur_ptr is used in the INSERT (INS) to qualify the column names in the retrieval of the next set of values from spool.

The SELECT retrieves its rows from the table and puts them into spool. Lastly, all SQL after the DO uses a semi-colon to designate where one statement ends and the next one begins. Triggers use a technique similar to this as the before image and the after image for changed rows. The difference here is that the image in spool does not change, it is the FOR that establishes the name for the pointer and the SELECT that makes the rows available for use in the procedure.

This is not a complicated FOR example. Additionally, it does not contain an updateable cursor. It is our recommendation that if you chose to pursue either of these types of procedures, you use the reference manual as your guide.

ITERATE Statement

The ITERATE statement is used to skip all statements after the ITERATE statement. It loops back and repeats all logic from the label name specified in a loop back to the ITERATE statement. The label name must be associated with either a WHILE, FOR, or LOOP statement within which the ITERATE statement is located. However, the label name may not be associated with a BEGIN-END block of the procedure body.

The syntax for the ITERATE statement:

ITERATE <label-name> ;

The next procedure example delays for a number of loops that is entered as an input parameter:

The ITERATE causes only the SET command to execute until the parm_val decrements to 2. Then, it fails the IF test and instead of repeating, falls through to the INSERT. After that, it loops back to the WHILE test and since it is equal to 1, it continues through for the last time. Once again, it fails the IF test, so the iteration loop is not used and again the INSERT in performed. Notice also that the SQLSTATE and SQLCODE status variables are used to make sure that the INSERT works. If it fails, the SQL status code is sent back to the user as an aid to the debug process.

PRINT Statement

The PRINT statement is used for testing and debugging of procedures. Its output values are printed in the DBS I/O Window (screen 5). No output is generated unless the procedure is created with PRINT option enabled. It is not a convenient mode of debug, but if all else fails, it might be the only technique available.

The output is generated in the following format:

<user-name> | <session-id> | <db-name>.<sp-name> | #<line-number> | <timestamp> |<user-specifiedstring>

The syntax for the PRINT statement follows:

PRINT <string-literal>, <identifier> ;

The string literal is used to identify each printed value. The PRINT output might be mingled with output from other procedures and utilities running under Teradata.

The identifier is the local variable or parameter value needed to help debug the procedure.

Since it is so difficult to get to the output of the PRINT command, an OUT parameter is probably an easier way to obtain debug information, as seen in the previous procedure example.

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

Teradata Topics