Teradata Stored Procedures - Introduction

• Stored Procedure is a Teradata object defined in database or user space.
   
• Stored Procedure contains two types of statements:
• SQL statements
• SPL statements(Stored Procedure Language)
   

• Stored Procedures need Perm space.
   
• Stored Procedures are compiled, Stored and Executed on the server side.
   
• The Source code can also be stored optionally

• Restrictions on using Stored Procedures:
• Stored Procedure cannot contain DDL and DCL Statements.
• Stored Procedure cannot EXPLAIN or USING statements.

• We can pass parameters to stored procedures. Parameters can be input, output or input/output. The type of parameter is indicated by the words IN, OUT or INOUT.

• IN - Values that are supplied to the stored procedure at execution
• OUT - Values that are returned from the stored procedure to the user at call completion 
• INOUT - Values that are both supplied to and returned from the stored procedure

• Stored Procedure can contain up to 1024 parameters.

• Example:

Creating a simple procedure:

CREATE PROCEDURE

TEST_PROCS.TEST_PROC

( IN var1 INTEGER, OUT var2 CHAR(40)) --> IN indicates the input parameter, OUT indicates the output parameter. Data types should be provided.

BEGIN

IF var1 = 0 THEN

SET var2 = 'THE VALUE ENTERED IS ZERO';  --> SET is used to assign values to parameters or local variables. Here we set values to an output variable.

ELSEIF var1 < 0 THEN

SET var2 = 'THE VALUE IS NEGATIVE'; --> SET also ends with a semicolon

ELSE --> IF statement may have multiple ELSEIF, but only one ELSE

SET var2 = 'THE VALUE IS POSITIVE';

END IF;  --> IF statement must be terminated with END IF

END; -->  Stored Procedure body is enclosed inside BEGIN and END. END should end with a semi colon.

Imp Note: When Using SET to assign values to parameters the Input type parameters(IN) should always be on the right side of = sign (.ie should be the sending fields)  and output (OUT)  type parameters should always be on the left side of =.(.ie should be receiving fields)

If this is not followed we would get an error.

Calling a simple procedure:

CALL TEST_PROCS.TEST_PROC(2,variable2) --> All the parameters must be represented in the CALL statement. Note that the output parameter must be specified using the parameter name(variable2 in the example). The Output Parameter value is reported similar to a column value from a table.

Result:

variable2

THE VALUE IS POSITIVE                  

• Arguments that are passed to the call statement can be defined using constants, variable or both

We can call the same procedure as shown in above example as below:

CALL SUPPORT_PROCS.TEST_PROC(3*5,Column2)

Result:

var2

THE VALUE IS POSITIVE                  

CALL SUPPORT_PROCS.TEST_PROC(-2+1,Column2)

Result:

var2

THE VALUE IS NEGATIVE