DECLARING VARIABLES INSIDE A STORED PROCEDURE LOOP

General
Enthusiast

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

2 REPLIES
Enthusiast

Re: DECLARING VARIABLES INSIDE A STORED PROCEDURE LOOP

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 .

Senior Apprentice

Re: DECLARING VARIABLES INSIDE A STORED PROCEDURE LOOP

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;