Teradata Procedures are set of command that will be executed step by step and are grouped together to perform a logical task

Teradata Procedures

As in any other database system, procedures are set of command that will be executed step by step and are grouped together to perform a logical task.
Teradata procedure support most of the operations,for example

• Insert / Update / Merge / Delete
• Drop / Truncate / Create Table/View
• Collect Statistics
• Procedure Calls
• Iteration[For/While loop]
• Cursors
• Variable manipulation
• Conditions [If/ELSE]

Teradata procedures can be of two types:

• General Procedure: Procedure that performs some action in the backgorund
• Dynamic procedure: Procedures that returns resultset/query result

Syntax 1: Procedure Block

REPLACE PROCEDURE [database_name.procedurename] [(Input_variable [datatype],Output_variable [datatype])]
[DYNAMIC RESULT SETS 1]
BEGIN
DECLARE var1 [datatype];
DECLARE var2 [datatype];

--Error handling block
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
 ROLLBACK;
 -- error handlig code
END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
BEGIN
-- error handlig code
END;

--Procedure Logic --

END;
  
Note: REPLACE command will create PROCEDURE if its already not present.

Syntax 2: Calling Procedure

CALL [database_name.procedurename] [(parameters)];

Syntax 3: Compiling Procedure

.COMPILE FILE [procedure_file];

Syntax 4: Get Procedure Code

SHOW PROCEDURE [database_name.procedurename];

Syntax 5: Drop Procedure

DROP PROCEDURE [database_name.procedurename];

Example 1: Sample Procedure

REPLACE PROCEDURE tutorial_user.sample_procedure(out return_code Varchar(20))
BEGIN

   /* Variable declaration */
   DECLARE lv_proc_step VARCHAR(1000);
   DECLARE lv_proc_name VARCHAR(50) DEFAULT 'sample_procedure';
   
   /* Continue Handler for Table does not exist */
   DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'  
   BEGIN 
     /* Even if Drop table fails,continue to the next step*/
   END; 

  /* Exit Handler for any SQL exception other than table does not exist */
   DECLARE EXIT HANDLER FOR SQLEXCEPTION
   BEGIN
        ROLLBACK;
        SET return_code = 'ERROR';
        INSERT INTO tutorial_db.runtime_error
        ( session_nr,
          procedure_name,
          proc_step_txt,
          error_cd
        )
        VALUES
        (
          SESSION,
          lv_proc_name,
          lv_proc_step,
          SQLCODE
        );

   END;

 SET return_code='Running';

   SET lv_proc_step='Dropping table';
   DROP TABLE tutorial_db.department;


   SET lv_proc_step='Creating table';
   CREATE MULTISET TABLE tutorial_db.department
   (
     dept_no INTEGER,
     department_name VARCHAR(30),
     loc_name  VARCHAR(30)
   )
   PRIMARY INDEX(dept_no);


   SET lv_proc_step='Inserting data into table';
   INSERT INTO tutorial_db.department VALUES(100, 'ACCOUNTS', 'JAIPUR');
   INSERT INTO tutorial_db.department VALUES(200, 'R & D', 'NEW DELHI');
   INSERT INTO tutorial_db.department VALUES(300, 'SALES', 'BENGALURU');
   INSERT INTO tutorial_db.department VALUES(400, 'INFORMATION TECHNOLOGY', 'BHUBANESWAR');


 SET return_code='Success';

END; 

Syntax: Procedure call

call tutorial_user.sample_procedure(A);

Example 2: Sample Dynamic Procedure

REPLACE PROCEDURE tutorial_user.sample_dynamic_proc(IN optionv INT)
DYNAMIC RESULT SETS 1

BEGIN

/* Declaring dynamic cursor to get output as resultset  */
    DECLARE select_1 CURSOR WITH RETURN ONLY TO CLIENT FOR
    SELECT session;
    
    DECLARE select_2 CURSOR WITH RETURN ONLY TO CLIENT FOR
    SELECT database;
    
    DECLARE select_3 CURSOR WITH RETURN ONLY TO CLIENT FOR
    SELECT User;
    
    DECLARE select_4 CURSOR WITH RETURN ONLY TO CLIENT FOR
    SELECT * from tutorial_db.department;
    
/* Opening dynamic cursor using passed parameter */
    CASE optionv
    WHEN 1 THEN OPEN select_1;
    WHEN 2 THEN OPEN select_2;
    WHEN 3 THEN OPEN select_3;
    ELSE OPEN select_4;
    END CASE;
  
END;

Syntax: Procedure call

call tutorial_user.sample_dynamic_proc(4);