15.00 - 15.10 - SQL Stored Procedure Example - Teradata Database

Teradata Database Introduction to Teradata

Product
Teradata Database
Release Number
15.00
15.10
Content Type
User Guide
Publication ID
B035-1091-151K
Language
English (United States)
Last Update
2018-09-25

Assume you want to create an SQL stored procedure named NewProc that you can use to add new employees to the Employee table and retrieve the department name of the department to which the employee belongs. You can also report an error, in case the row that you are trying to insert already exists, and handle that error condition.

The following SQL stored procedure definition includes nested, labeled compound statements. The compound statement labeled L3 is nested within the outer compound statement L1. Note that the compound statement labeled L2 is the handler action clause of the condition handler.

This SQL stored procedure defines parameters that must be filled in each time it is called (executed).

CREATE PROCEDURE NewProc (IN name CHAR(12),
              IN num INTEGER,
              IN dept INTEGER,
              OUT dname CHAR(10),
              INOUT p1 VARCHAR(30))
L1: BEGIN
   DECLARE CONTINUE HANDLER FOR SQLSTATE value '23505'
    L2: BEGIN
        SET p1='Duplicate Row'
		;
    END L2;
        L3: BEGIN
        INSERT INTO Employee (EmpName, EmpNo, DeptNo)
        VALUES (name, num, dept)
	;
       
        SELECT DeptName 
        INTO dname FROM Department
        WHERE DeptNo = dept;
        IF SQLCODE <> 0 THEN LEAVE L3;
        ...
        END L3
		 ;
END L1
;