Categories
Teradata Stored Procedures

Teradata Stored Procedures Parameters- IN , INOUT , OUT

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 the above proc 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:

inout_dbname out_dbname
set IN value to INOUT–>HELLO set INOUT value to OUT–>WORLD
Categories
Teradata Stored Procedures

Teradata stored procedure activity count

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.

Categories
Teradata Stored Procedures

Teradata Stored Procedure Access Rights

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. Before going forward make sure to read it: SQL SECURITY OPTION FOR STORED PROCEDURE 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.

In case you need more clarity, feel free to leave a comment.

Categories
Teradata Stored Procedures

Stop Teradata SQL Assistant from getting Timeout

So often it happens that we execute SQL queries which dump the data into some volatile tables however when we return and check data in volatile table , we see that Teradata SQL Assistant connection timed out and has been disconnected. Then again we have to run same queries and do our analysis. So to overcome this why not run a process which should not consume any AMPCPU and also make sure that your connection stay active. We will create a stored procedure and will run it. And we can continue working in other tabs in Teradata SQL Assistant. You can use below Stored Procedure to make sure your connection stay active for the time you want it to be.

REPLACE PROCEDURE STAY_ACTIVE()
BEGIN
DECLARE VAR_CNTR1 TIMESTAMP;
DECLARE VAR_CNTR2 TIMESTAMP;
SET VAR_CNTR1 = CURRENT_TIMESTAMP(0);
SET VAR_CNTR2 = CURRENT_TIMESTAMP(0) + INTERVAL '600' SECOND;
REPEAT
SET VAR_CNTR1 = CURRENT_TIMESTAMP(0);
UNTIL VAR_CNTR1 > VAR_CNTR2
END REPEAT;
END;

The above proc will run for 600 seconds. You can modify the values like ‘600’ SECOND, ’60’ MINUTE, ‘6’ HOUR etc. To run the proc just call it.

CALL STAY_ACTIVE();

 

Categories
Teradata Stored Procedures

How to remove numbers and special characters from string in SQL in Teradata

My friend recently asked me how to remove special characters and numbers from values present in any column in table in Teradata. We often come across situation when we need only characters as the value and if there is anything else we should remove it. It can be some Data Mining requirement or Data Cleansing. So we can achieve this by several methods. We will discuss various possible methods in our subsequent posts. Today, we will see how we can achieve this using Stored Procedure.

Let’s create a table which will hold the data and we will call stored procedure to update the value in column in this table with desired values.

CREATE VOLATILE TABLE TBL_REPL
(
COL1 VARCHAR(20),
COL2 VARCHAR(20)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO TBL_REPL
SEL 'AB@CD', 'AB@CD';

INSERT INTO TBL_REPL
SEL 'AB@CDE#F', 'AB@CDE#F';

INSERT INTO TBL_REPL
SEL 'AB@CD123%%89E#F', 'AB@CD123%%89E#F';

INSERT INTO TBL_REPL
SEL 'AB@CDE#F', 'AB@CDE#F';

SEL * FROM TBL_REPL;

Data in table with special characters , numbers along with alphabets
Data in table with special characters , numbers along with alphabets

We will now write a stored procedure which will update COL2 with value without numbers and special characters. So after executing the stored procedure we should have only alphabets in the COL2.

REPLACE PROCEDURE PROC_TBL_REPL(IN MYTABLE VARCHAR(30), IN MYCOLUMNID VARCHAR(30), IN MYCOLUMNREP VARCHAR(30))
BEGIN
DECLARE DESC_ID2 VARCHAR(20);
DECLARE DESC_DETAIL2 VARCHAR(20);
DECLARE CNT SMALLINT;
DECLARE TOT_LEN SMALLINT;
DECLARE NEW_VAL VARCHAR(50);
DECLARE CHANGED_VAL VARCHAR(50);
DECLARE MYSQL_STR VARCHAR(10000);
DECLARE MYSQL_STR1 VARCHAR(10000);
DECLARE CURSOR_TST_CUR CURSOR FOR STMT1;
SET MYSQL_STR='SEL '||MYCOLUMNID||','||MYCOLUMNREP||' FROM '||MYTABLE||'';
PREPARE STMT1 FROM MYSQL_STR;
OPEN CURSOR_TST_CUR;
LABEL1:
LOOP
FETCH CURSOR_TST_CUR INTO DESC_ID2,DESC_DETAIL2;
IF (SQLSTATE = '02000') THEN
LEAVE LABEL1;
END IF ;
SET CNT=1;
SET NEW_VAL='';
SET CHANGED_VAL='';
SEL CHAR_LENGTH(DESC_DETAIL2) INTO TOT_LEN;
LABEL:
WHILE (CNT <= TOT_LEN+1)
DO
SEL CASE WHEN (ASCII(SUBSTR(DESC_ID2,CNT,1)) BETWEEN 65 AND 90) THEN SUBSTR(DESC_ID2,CNT,1) ELSE ' ' END INTO NEW_VAL;
SET CHANGED_VAL = CHANGED_VAL||''||NEW_VAL;
SET CNT=CNT+1;
END WHILE ;
SET MYSQL_STR1='UPDATE '||MYTABLE||' SET '||MYCOLUMNREP||' = '''||CHANGED_VAL||''' WHERE '||MYCOLUMNID||'='''||DESC_ID2||'''';
CALL DBC.SysExecSQL(MYSQL_STR1);
END LOOP LABEL1;
CLOSE CURSOR_TST_CUR;
END ;

Run the proc by giving below command:
CALL PROC_TBL_REPL('TBL_REPL','COL1','COL2');
Now check the records in table.
SEL * FROM TBL_REPL;

Data in table with only alphabets
Data in table with only alphabets

Let’s see how we actually achieved this. We created a stored procedure which takes tablename, key column and column for which we have to replace values as input parameters. We traverse the complete string in column and check the ASCII value for characters. For alphabets, ASCII value range from 65 to 90. You may use UPPER function to convert the string to upper case and then apply ASCII function. If the ASCII value is not in range then replacing it with ‘ ‘ i.e. BLANK value. And once the complete string is traversed; we update the COL2 value with new value.

Categories
Teradata Stored Procedures

Use Cursors in Stored Procedure in Teradata to return Resultset

In the previous post, we learned how to CREATE a Stored Procedure in Teradata.

Use Cursors in Stored Procedure in Teradata to return Resultset
Use Cursors in Stored Procedure in Teradata to return Resultset

In this post, 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.

Categories
Teradata Stored Procedures

CREATE STORED PROCEDURE EXAMPLE IN TERADATA

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.

Categories
Teradata Stored Procedures

Run all SQLs in stored procedure even if you get error

Sometimes we may want Stored Procedure to execute all SQL statements even if there is error. Generally, Stored Procedure exit as soon as it encounters any error. However we may come across situation where we wish to execute all the SQL statements in Stored Procedure even if an error is encountered. We can achieve this my adding a CONTINUE handler for SQLEXCEPTION. If you wish to add the exception details , you can add it in handler. Lets look at the below example:

REPLACE PROCEDURE TST_HANDLER(OUT ENAME VARCHAR(30))
SQL SECURITY OWNER
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLEXCEPTION
BEGIN
END;
SEL FNAME INTO :ENAME FROM EMPLOYEE WHERE FNAME='JACOB';
SEL FNAME INTO :ENAME FROM EMPLOYE WHERE FNAME='JOHN';
SEL FNAME INTO :ENAME FROM EMPLOYEE WHERE FNAME='GREEN';
END;

To run the procedure :

CALL TST_HANDLER(ENAME) ;

In the above example, in the second SEL statement , the tablename is wrong hence it will fail with Table Does Not Exist error. However, since we have added CONTINUE handler for SQLEXCEPTION hence, it will continue executing further SQL statements.

Categories
Teradata Stored Procedures

SQL SECURITY options available in Stored Procedures in Teradata ?

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:

1) 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.

2) 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.

3) 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.

4) 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.