Stored Procedures in Teradata!!

Database
Fan

Stored Procedures in Teradata!!

Hi Friends,

I just need to know how to write SP in Teradata. Plz explain me with an example.

Regards
Rakesh
7 REPLIES
Enthusiast

Re: Stored Procedures in Teradata!!

Hi Rakhi,

Lot of procedures are available in the net.
Below is the one for your reference:

CREATE PROCEDURE spSample1()
BEGIN
L1: BEGIN
DECLARE vName CHARACTER(30);
DECLARE vAmt INTEGER;
DECLARE EmpCursor CURSOR FOR
SELECT EmpName, Salary
FROM EmpDetails
ORDER BY DeptCode;
DECLARE DeptCursor CURSOR FOR
SELECT DeptName
FROM Department;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
BEGIN
OPEN EmpCursor;

END;


END L1;
END;
Enthusiast

Re: Stored Procedures in Teradata!!

Hi Sunar

I too am interested in Stored Procedures and have a question

I understand how to create a simple one but cannot understand where to implement them

say you created the stored procedure you gave above.

I know I won't get the syntax correct but can you use it the following way

sel * from table1
where (field1, field2) in (spsample1)

I suppose I am wondering how you can call it up during other queries, during joins etc. I dont understand how/where you incorporate it into existing SQL.

Can you give me a simple example where you would use call a stored procedure within some existing query?

Enthusiast

Re: Stored Procedures in Teradata!!

Stored procedures cannot be called within a query, it is more like a parameterized script that you call and is executed independently. These procedures can include DDL as well as DML statements and are usually meant for data/structure/metadata creation/maintaininece, etc.

If I remember correctly, the syntax for calling on a stored procedure is:
execute ( parameter 1, parameter2, parameter3,..)

I've just started working on Teradata so not sure if this syntax works correctly - but I know it works on Oracle at least.
Enthusiast

Re: Stored Procedures in Teradata!!

So if I wanted to build a results table using a loop that was more complicated than a recursive query could handle, then I would need to build a Stored Procedure by writing the select statement along with the joins, OLAP functions, loopa etc all as part of one stored procedure?

Then execute the stored procedure?

Below is the wrong syntax i know but for example

---------
create storedprocedure1
while {some condition1} <> 2
then insert into table3 {some rules}
end stored procedure
---------
then EXE storedprocedure1
---------
Simple but is that how you would use it rather than executing it with a query?
Enthusiast

Re: Stored Procedures in Teradata!!

The CALL statement is used to invoke a stored procedure.

The EXECUTE statement is used to invoke a macro.

Enthusiast

Re: Stored Procedures in Teradata!!

Hi Jim and thanks

Ok so same question but CALL instead of EXE
gam
Fan

Re: Stored Procedures in Teradata!!

Hello,

I've read this topic and I notice vickyejain information about using SP within query, however I'm curious if anything has changed in those last 2 years in TD about this. If not, maybe someone knows and would like to share some trick how to raplace SP.

For example at below query:

select my_function(my_table.kol1) from my_table

Thanks