|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/13/2009 10:49:55 PM
Posts: 2,
Visits: 3
|
|
Hi All,
I am facing some wall in the stored procedure in Teradata. Please help me.
The definition of SP is as follows:
Create procedure 123(IN ABC varchar(1000))
(
begin
Define I integer;
Define query Varchar (1000);
Set I = 1;
Set query=ABC;
While (I < 36)
DBC.EXEC(:query);
set I= I+1;
END While;
)
sorry if the definition of Procedure is wrong
My doubt is if my input is 'Select A from DB1. Table where B= ;'
I want to pass 'I' in the procedure to this query for every loop....
How to do it in teradata ?
Thanks in Advance
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/8/2009 1:16:14 PM
Posts: 79,
Visits: 105
|
|
Hi,
It is similar like you use the variable 'query' in DBC.EXEC().
You can make use of concatenation operator ( '||' ) while building a dynamic query which involves in using more than one local variable in a stored procedure.
Try like this:
DBC.SysExecSQL(:query|| :I ||';');
SysExecSQL invokes dynamic SQL, and important point to be noted while using dynamic SQL is, the user who is executing SP should be the immediate owner of SP.
Your input parameter should be like ''Select A from DB1.Table where B= ' (without semicolon symbol).
There are few other limitations in using dynamic SQL in SP, do refer Teradata Stored Procedure PDFs in www.info.teradata.com.
Hope this information helps you.
Regards,
Balamurugan
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/13/2009 10:49:55 PM
Posts: 2,
Visits: 3
|
|
thanks ,
but don't we have anything in teradata like & in C/C++..
It would be better if the variable in the passing string itself....
|
|
|
|