All Forums Tools
talal_ahmad 3 posts Joined 06/06
27 Jun 2006
Stored Procedures

I have heard some people talking about the fact that usage of stored procedures is not recommended in Teradata.Being new to Teradata i really dont know much about all this and i would like some guidance on this.Is this true that stored procedures are not recommended in Teradata and if yes what is the reason for that?

Fred 1096 posts Joined 08/04
27 Jun 2006

It's really not Stored Procedures that are the issue, it's the "cursor processing" you want to minimize. Whether you use embedded SQL in an application program or a Stored Procedure, cursor logic is sequential - so you don't get the benefits of Teradata's massively parallel architecture.Consider a simple case where you only want to process a small subset of the rows in a table. The SQL for your cursor could include a WHERE clause to filter out the other rows. Or the cursor could return all rows and the procedural code could check to see if the row should be processed or skipped. In other databases, using the WHERE clause should perform somewhat better; but in Teradata that difference can be huge (parallel processing versus serial).

j355ga 100 posts Joined 12/05
27 Jun 2006

Another disadvantage is the inability of Teradata stored procedures to return a data-set. The typical approach to circumvent this limitation is to insert to a temporary table and then - while maintaining the connection - retrieve said temporary table. This is an issue when implementing stored procedures used in a web-page. I've heard that Teradata will correct this limitation in V2R7.

Jeff

philyq1 1 post Joined 07/06
06 Jul 2006

In debugging a procedure, how do I see the return value using Teradata sql assistant. I know you can print a value for debugging but I don't know how to create a procedure with print option enabled. I need to see the result of number_days.call pqualtier.get_number_days(1060528,1060528,number_days)create procedure pqualtier.get_number_days(IN pick_date INTEGER, IN deliv_date INTEGER, OUT number_days INTEGER)begindeclare weekday_cnt, holiday_cnt INTEGER DEFAULT 0;IF pick_date <= 0 OR deliv_date <= 0 THEN set number_days = 0;ELSEIF pick_date = deliv_date THENset number_days = 1;ELSEselect count(*) INTO :weekday_cnt from ltl.calendar_date_ref where clndr_dt > :pick_date and clndr_dt <= :deliv_date and day_of_wk_nbr between 2 and 6;select count(*) INTO :holiday_cnt from ltl.corporate_holiday where clndr_dt > :pick_date and clndr_dt <= :deliv_date;set number_days = weekday_cnt - holiday_cnt;END IF;end;

BBR2 96 posts Joined 12/04
06 Jul 2006

Configure your ODBC connection to return the result set as Start > run > odbcad32This launches ODBC Adminstrator windowSelect the connection setting (Your ODBC connection setting for accessing TD)This will launch ODBC Driver Setup for Teradata RDBMSSelect Options - Launches teradata ODBC Driver OptionsCheck "Return Output Parameters As ResultSet"Vinay

sfyeu1 5 posts Joined 08/06
16 Aug 2006

I'm studying the SQL Reference: Stored Procedure and Embedded SQL Release V2R6.1. It mainly teach us how to write the stored procedure. Also, mention the stored procedure can provide Better Performance, Better Application Maintenance, Better Transaction Control. So, I think Teradata suggest us to write the Stored Procedure.

You must sign in to leave a comment.