SQL Stored Procedure Example - Advanced SQL Engine - Teradata Database

Database Introduction

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qia1556235689628.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1091
lifecycle
previous
Product Category
Teradata Vantage™

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
;