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?
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).
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.
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.
create procedure pqualtier.get_number_days(IN pick_date INTEGER, IN deliv_date INTEGER, OUT number_days INTEGER) begin declare 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 THEN set number_days = 1; ELSE select 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;
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.