SQL Stored Procedure Example - Teradata Vantage - Analytics Database

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 name of the department to which the employee belongs. You can also report an error if 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. The compound statement labeled L2 is the handler action clause of the condition handler.

This SQL stored procedure defines parameters whose values you must provide each time you call it.

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
;