Teradata Stored Procedure for beginners

Stored Procedures in Teradata are very useful powerful objects especially with every new Teradata versions released. With so many options available for condition handling and dynamic sql, Teradata Stored Procedures makes a SQL Developer life lot better.
Let us take an example to see how we can create a new Stored Procedure in Teradata. In the below example, we will create a stored procedure which will insert few records in some table. The idea is to become familiar with the Stored Procedure syntax. So let’s create a table in which we will insert rows within a stored procedure.

CREATE MULTISET VOLATILE TABLE US_STATES , NO JOURNAL, NO LOG,NO FALLBACK
 (
 STATE_NAME VARCHAR(13),
 STATE_ABBR CHAR(2),
 STATE_CAPITAL VARCHAR(13),
 STATE_CITY VARCHAR(13)
 )
 PRIMARY INDEX(STATE_NAME)
 ON COMMIT PRESERVE ROWS
 ;

Now let’s create a Stored Procedure which will insert some rows into the table.

CREATE PROCEDURE US_STATE_1 ()
 BEGIN
 INSERT INTO US_STATES VALUES('Alabama','AL','Montgomery','Birmingham');
 INSERT INTO US_STATES VALUES('Alaska','AK','Juneau','Anchorage');
 INSERT INTO US_STATES VALUES('Arizona','AZ','Phoenix','Phoenix');
 INSERT INTO US_STATES VALUES('Arkansas','AR','LittleRock','LittleRock');
 INSERT INTO US_STATES VALUES('California','CA','Sacramento','LosAngeles');
 END;

Try creating this proc by running the above Create Procedure definition.
The proc will be successfully completed. So to create a Stored Procedure you need to give CREATE PROCEDURE PROCEDURE_NAME. You can then give input/output parameters too. We will see about parameters in following posts. Then you can give your desired SQL statements with in BEGIN … END clause.

To run the proc: CALL US_STATE_1();

Once done, you can see message : CALL completed.

Now check the data in the table.

SELECT * from US_STATES;

If your proc was successfully compiled and successfully completed, then you should be able to see 5 rows in the table.

Kudos, you just created your first proc and executed it successfully.

Stored Procedure Access Rights – SQL Security option

In Teradata, Stored Procedures have 4 different options for SQL SECURITY. It mainly has to do with access rights , privileges or permissions which will be granted to Procedure while calling it. Let us see the four options in Teradata for SQL Security:

SQL SECURITY CREATOR:

If you select this option then all the rights/privileges available to the procedure will be of the CREATOR of the procedure. So if user TD_USER has created the Stored Procedure then procedure will have access rights that of CREATOR ‘TD_USER’ in this case. Also, the volatile tables and default database will be TD_USER.

SQL SECURITY OWNER:

If you select this option then all the rights/privileges available to the procedure will be of the OWNER of the procedure. So if user TD_USER has created the Stored Procedure in TD_PROC schema, then procedure will have access rights that of OWNER ‘TD_PROC’ in this case. Also, the volatile tables and default database will be TD_PROC.

SQL SECURITY DEFINER:

If you select this option then all the rights/privileges available to the procedure will be of the DEFINER, the default database of the user invoking the procedure. So if user TD_USER has created the Stored Procedure in TD_PROC schema however the default database for TD_USER is TD_DB_PROCS then procedure will have access rights that of  ‘TD_DB_PROCS’ in this case. Also, the volatile tables and default database will be TD_DB_PROCS.

SQL SECURITY INVOKER:

If you select this option then all the rights/privileges available to the procedure will be of the INVOKER of the procedure i.e. the user who is calling/executing the procedure. So if user TD_PROC_USER is executing the Stored Procedure then procedure will have access rights that of INVOKER ‘TD_PROC_USER’ in this case. Also, the volatile tables and default database will be TD_PROC_USER.

Default option is DEFINER for SQL SECURITY.

When you write a stored procedure a very common consideration is regarding the access rights which should be granted to it. I wrote a post long back regarding SQL SECURITY option available in Teradata. So now you are aware of four options available in Teradata for assigning proper permissions to it. Lets see a small example to check it.

=== Compile This Stored Procedure in Teradata ===

REPLACE PROCEDURE ACCESSRIGHTSINFO (OUT DBNAME VARCHAR(30))
SQL SECURITY DEFINER /* [OWNER, INVOKER, CREATOR] */
BEGIN
SEL DATABASE INTO :DBNAME;
END;

=== Execute it to check the value of DEFINER ===
CALL ACCESSRIGHTSINFO(DBNAME);

Change value to OWNER, INVOKER & CREATOR one-by-one and see the output returned.
With the output you can relate more to the concept of SQL SECURITY OPTION.

Cursors in Stored Procedures

Now, we will see how to return resultset by using CURSORs in STORED PROCEDURES. So in the below mentioned stored procedure example, we will insert some data into the table and will return the resultset. Let’s create one table first which we will use in this example.

CREATE MULTISET VOLATILE TABLE US_STATES , NO JOURNAL, NO LOG,NO FALLBACK
 (
 STATE_NAME VARCHAR(13),
 STATE_ABBR CHAR(2),
 STATE_CAPITAL VARCHAR(13),
 STATE_CITY VARCHAR(13)
 )
 PRIMARY INDEX(STATE_NAME)
 ON COMMIT PRESERVE ROWS
 ;

Now let’s create one Stored Procedure which will INSERT some records into this table and will then return the resultset having all the records from this table.

REPLACE PROCEDURE US_STATE_2 ()
 DYNAMIC RESULT SETS 1
 BEGIN
 DECLARE US_STATES_DETAILS CURSOR WITH RETURN ONLY FOR SEL * FROM US_STATES;
 INSERT INTO US_STATES VALUES('Alabama','AL','Montgomery','Birmingham');
 INSERT INTO US_STATES VALUES('Alaska','AK','Juneau','Anchorage');
 INSERT INTO US_STATES VALUES('Arizona','AZ','Phoenix','Phoenix');
 INSERT INTO US_STATES VALUES('Arkansas','AR','LittleRock','LittleRock');
 INSERT INTO US_STATES VALUES('California','CA','Sacramento','LosAngeles');
 OPEN US_STATES_DETAILS;
 END;

Create this procedure and it should complete successfully.
To execute this procedure, CALL US_STATE_2();

Few points to remember:
DYNAMIC RESULT SETS: It is used to specify number of result sets that will be returned to the caller/client running the Stored Procedures.
CURSOR: CURSOR is data structure which holds result set of the query in Stored Procedures.
DECLARE US_STATES_DETAILS CURSOR WITH RETURN ONLY FOR SEL * FROM US_STATES: This statement DECLARES the cursor with name US_STATES_DETAILS and returns the result set to client/caller for the query SEL * FROM US_STATES.
OPEN US_STATES_DETAILS: This command OPENS the CURSOR to execute the SELECT statement.

Kudos , after this lesson you know how to CREATE a STORED Procedure in Teradata and Return the resultset to the caller.

Stored Procedures are basically blocks of SQL code which performs some functionality. However in some cases depending on situation , you may want stored procedure to perform some tasks as per your requirement. Parameters in Stored Procedure helps user to pass some information to Stored Procedure and get the corresponding result.
Teradata Stored Procedure can have 3 types of parameters :
IN: INPUT parameter is used to pass value to Stored Procedures. However
it do not return any value.
INOUT: IN-OUT parameter is used to pass value to Stored Procedures. And it can return any value too.
OUT: OUT Parameter is used to return some value from Stored Procedure only.
Let us see an example below:

REPLACE PROCEDURE CheckParams (IN in_dbname VARCHAR(50), INOUT inout_dbname VARCHAR(50), OUT out_dbname VARCHAR(50))
SQL SECURITY INVOKER
BEGIN
SET out_dbname='set INOUT value to OUT-->'||inout_dbname;
SET inout_dbname='set IN value to INOUT-->'||in_dbname;
END;

In this stored procedure we are setting value of INOUT value to OUT parameter.
And value of IN parameter to INOUT. IN parameter is not returned.

CALL CheckParams (CAST(‘HELLO’ AS CHAR(50)) ,CAST(‘WORLD’ AS CHAR(50)),DBNAME);

Output:

Activity_COUNT is used in Teradata Stored Procedure to keep track of the number of records impacted by any SQL Query. To check it, just use the ACTIVITY_COUNT keyword after any SQL Query which impacts rows. It is initialised to 0 at the start and value is updated after every single SQL query. Lets see an example below to see how Activity_Count works in Teradata:

REPLACE PROCEDURE SP_ACTIVITYCOUNT (OUT tot_count_delete INTEGER)
 SQL SECURITY INVOKER
 BEGIN
 CREATE MULTISET VOLATILE TABLE vt_test1
 (
 name VARCHAR(20)
 )
 ON COMMIT PRESERVE ROWS;

INSERT INTO vt_test1 VALUES ('Mike');
 INSERT INTO vt_test1 VALUES ('Megan');
 INSERT INTO vt_test1 VALUES ('Morgan');
 INSERT INTO vt_test1 VALUES ('Mona');

DELETE FROM vt_test1 WHERE name LIKE 'M%n';
 SET tot_count_delete = ACTIVITY_COUNT;
 END;

CALL SP_ACTIVITYCOUNT (tot_count_delete);

Output: 2 [ Because Megan and Morgan were deleted ]

Activity_Count is very useful especially for logical decisions and in debugging.