teradata stored procedure


teradata stored procedure

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))
Define I integer;
Define query Varchar (1000);
Set I = 1;
Set query=ABC;
While (I < 36)
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

Re: teradata stored procedure


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.


Re: teradata stored procedure

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....