Stored procedure

Database
Enthusiast

Stored procedure

Hello i need help on creating a Stored procedure from the following

here is the view that is available please help me to create store procedure out of it......

Replace view view_name

as

sel col_1, col_2..........column_15

from

(

sel col_12, ...............col_15

from

tablename A1

left outer join

(

sel col1,col2............col15

from tablenme_2 A2

inner join

table_12221 A3

on A2.condition_1 = A3.cond_1

)

left outer join

(

...

.........

.........)

Goes on further......with left outer joins.... and sub queries joinin some more tables...
8 REPLIES
Enthusiast

Re: Stored procedure

Hi,

Can you specify what exactly you are trying to do with stored procedure..?

Following is the simple syntax for SP:
CREATE/REPLACE PROCEDURE Database_Name.Procedure_Name
(IN In_Var1 INTEGER, OUT Out_Var1 CHAR(30))
BEGIN
---
---
---
END ;

To call a SP use "Call Database_Name.Procedure_Name(1,var3);"

If you like to explore more on SP do refer teradata PDFs.

Regards,
Balamurugan
Enthusiast

Re: Stored procedure

Hi,

Yes it should be included anywhere between BEGIN and END..
You can use view in a SP similar to any table...

like...

Select col1 from DB_name.view_name

You can also assign return value of a select statement into a local variable in procedure by using SELECT INTO statement...

Refer "SQL Stored Procedures and Embedded SQL" PDFs in http://www.info.teradata.com/DataWarehouse/eTeradata-BrowseBy-Results.cfm?pl=&PID=&title=%25&release...

or in any teradata version at www.info.teradata.com

Regards,

Balamurugan
Enthusiast

Re: Stored procedure

Hi,

It seems you are expecting to include your View DDL statement in SP.

Following are the few examples of DDL statements in various Mode:
1. Implicit Transaction Mode:
REPLACE PROCEDURE Proc_Name()
BEGIN
REPLACE VIEW View_Name as
(
--------
);
END;

2.Explicit Transaction Mode:
REPLACE PROCEDURE Proc_Name()
BEGIN
BEGIN TRANSACTION;
REPLACE VIEW Vie_Name as
(
--------
);
END TRANSACTION;
END;

3. ANSI Mode:
REPLACE PROCEDURE Proc_Name()
BEGIN
REPLACE VIEW Vie_Name as
(
--------
);
COMMIT WORK; /* This is required in ANSI MODE */
END;

Generic rule is DDL statement should be the last statement in transaction, but in normal Teradata mode this is not a problem, because each request by default is a stand alone transaction.

Regards,
Balamurugan
Enthusiast

Re: Stored procedure

here i need to create a new stored procedure ...for which the view is available...
in that case do i need to create a new sp??

i think what have u given is including a DDL statement in a already existing Stored procedure..? isnt so...
please correct me if im wrong!
Enthusiast

Re: Stored procedure

Hi,

The 'REPLACE PROCEDURE Proc_Name' command will create a new Procedure if the Proc_Name is not exist, if exist it will replace with the new procedure code with the same object name. It works similar to CREATE/REPLACE VIEW commands.

So the sample which I gave can also be used to create a new SP.

To make it simple you can rename 'REPLACE' with 'CREATE'.

please be more clear on what you required, I couldn't get you.

Regards,

Balamurugan
Enthusiast

Re: Stored procedure

Ok sure i will get back sooner with exactly what i need....even i got doubts at my end..wil get back once im clear about it ...
thanks for your tremendous help...appreciate it!
Enthusiast

Re: Stored procedure

since the view has already been created..can i do this..

REPLACE PROCEDURE Proc_Name()
BEGIN
sel * from view_name
END;
Enthusiast

Re: Stored procedure

You can do like this.. but by just doing 'select *' in SP you will not be able to see any return values unless you have any output variable... but you can try with other operation within SP.

Add ';' at the end of your select statement.

do execute your SP in your environment, so that u can see the result and correct your code then and there...