Executing STORED PROCEDURE in Teradata

You can execute a stored procedure by CALLing it.

Syntax:
CALL stored procedure name (parameters)


Suppose, 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;


You can call the procedure like,
CALL SP_Employee ('steve',12345,21,dname)