Teradata Stored Procedures Parameters

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 someRead More →

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_test1Read More →

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; ===Read More →

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 ProcedureRead More →

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 inRead More →

Use Cursors in Stored Procedure in Teradata to return Resultset

In the previous post, we learned how to CREATE a Stored Procedure in Teradata. 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 tableRead More →