ReadVitamin

If there is anything lacking in SQL to make it a complete programming language, Stored Procedures fulfills the same. Stored Procedures are written in SQL and consist of a set of control and condition handling statements. These features provide a server-based procedural interface to the Teradata Database for application programmers. What does this mean? It is simple, when you use Teradata from any client software such as SQL Assistant, then Teradata becomes the server and the SQL Assistant becomes the client. But when you call a Stored Procedure from SQL Assistant, at this time the Stored Procedure itself becomes the server to the SQL Assistant and client to Teradata DB. Thus it acts as an interface in between SQL Assistant and Teradata DB.

Things to NOTE while creating a Stored Procedure. 

1. Stored Procedure is a database ‘Object’ and DDL is used to create it.
2. The set of statements that forms the primary task of a Stored Procedure contains within, what is called a Stored Procedure body.
3. Stored Procedure body can be either a single state a compound statement.
4. Single statement procedure can contain only one looping mechanism or SQL statement, excluding Cursors.
5. Where as compound statements can have multiple statements but it has to be within the BEGIN and END block.
6. It is not a good practice to use DDL statements within a Stored Procedure.
7. It is stored in USER DATABASE space.
8. Can have parameters like IN, OUT and INOUT.

 Example:

CREATE PROCEDURE MyFirstProc (IN emp_number INTEGER,

                                             IN dept_number INTEGER,

                                             OUT dept_name CHAR(10),

                                             INOUT errstr VARCHAR(30))

BEGIN

        INSERT INTO Employee (EmpNo, DeptNo )

        VALUES (emp_number, dept_number);

        SELECT DeptName INTO dept_name FROM Department

        WHERE DeptNo = dept_number;

END;

To execute/call the above Stored Procedure:

CALL MyFirstProc (495, 211);