Creating STORED PROCEDURE in Teradata

Assume you want to define a stored procedure 'SP_Employee' 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, in case the row that you are trying to insert already exists, and handle that error condition.

The CREATE PROCEDURE statement looks like this:

CREATE PROCEDURE SP_Employee 
(IN name CHAR(20),
IN id INTEGER,
IN dept_no INTEGER,
OUT dname CHAR(10),
INOUT errstr VARCHAR(30))

BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE VALUE '23505'
    SET errstr = 'Duplicate Row.';

    INSERT INTO Employee (EmpName, EmpNo, DeptNo )
    VALUES (name, id, dept_no);

    SELECT DeptName
    INTO dname FROM Department
    WHERE DeptNo = dept;
END;