All Forums General
larun1616 29 posts Joined 09/14
13 Feb 2015
DECLARING VARIABLES INSIDE A STORED PROCEDURE LOOP

Hi
   Is there a way by which we can declare variables in a loop dynamically. I would like to create a vaiables like Var1,Var2,Var3....as and when a new loop begins. If the loop executed 4 times, four such variables has to be created. Kindly help me on this. Thanks.
 
Regards
Arun

Raja_KT 1246 posts Joined 07/09
14 Feb 2015

The document:
You must define a host variable between BEGIN DECLARE SECTION and END 

DECLARE SECTION statements.

 

It is a good practice to compartmentalize the code for ease of readability and code debugging... Oracle and othe databases that I know follow that standard too.

 

Even if it is to be dynamically declared, be it Unix, C, C++,Java....its declaration has to be cascaded from line(s) above it.

 

Maybe you can do thus, declare the variables in the declaration section with some default values for your business rules. Inside the loop, execute the code doing nothing or do an operation if it matches the required condition.

 

If you do not like the idea of constant default values, then there must be a source from where you should get Var1,Var2,Var3....as and when a new loop begins. You can use select..... into thence .

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
15 Feb 2015

Hi Arun,
if you know the maximum number of loops you might utilize an ARRAY in TD14. I've never done this, but it's possible according to the documentation.
Or you create a Volatile Table within your SP and insert a new row for every loop:

create volatile table vt(n int, variable ...)
unique primary index(n)  on commit preserve rows;

 

Dieter

You must sign in to leave a comment.