Modifying STORED PROCEDURE in Teradata

You can modify a stored procedure definition using the REPLACE PROCEDURE statement.

For example you are creating a stored procedure 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;



If we want to include the salary information also to the above one (After executing the above code) then,

The REPLACE PROCEDURE statement looks like this:

    REPLACE PROCEDURE SP_Employee
    (IN name CHAR(20),
    IN id INTEGER,
    IN dept_no INTEGER,
    IN salary DECIMAL(10,2),
    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,EmpSalary )
    VALUES (name, id, dept_no,salary);
     
    SELECT DeptName
    INTO dname FROM Department
    WHERE DeptNo = dept;
    END;