Loop in a Stored procedure

lionelcyril

Hi, I am using a SSRS report and the report is generated by executing a stored procedure. The procedure gets 4 input values a,b,c,d where values c and d is a string containing multiple values. I am executing a Complex SQL statement for all values of c and d. For this reason i am using nested loop:

A as INPUT
B as INPUT
C as INPUT
D as input
Cursor Cur as OUTPUT

Loop for c
	Loop for d
		execute the SQL Statement(select 
	END loop
END Loop

problem:
Now the results of the SQL statement has to be saved temporarily somewhere I am not allowed use permanent tables and I m struglling using temporary tables.
If i save the value in the cursor each time the loop runs the previous results get overwritten.

Lost User

Use a Table Variable to store temporary data. Syntax:

DECLARE @TableVar1 table(
Col1 int NOT NULL,
Col2 varchar(100),
Col3 datetime
)

And then you can use it just like a physical table, e.g:

INSERT INTO @TableVar1 (Col1, Col2, Col3) VALUES (10, 'Hello', GETDATE())

lionelcyril

Just to let you know.I am using this procedure in a package. Where should I write the temp table declaration?

Lost User

What does you mean by a package? Since you mentioned SSRS in your original post, I assumed the database to be SQL Server. AFAIK, SQL Server does not support packages, only Oracle does.

lionelcyril

Sorry I didnt mention earlier.. I am writing the stored procedure in oracle. I wanted to know if I can use a array and the store the results in the reference cursor which can be pulled by SSRS. but i dont know how to pass the values form array to reference

jschell

I doubt it is going to be efficient to try to incrementally retrieve values from Oracle and use them in sql server. So you have two steps. 1. Retreive the data from Oracle, all of it, and put it into a suitable data structure. 2. Use it in SSRS. First step would probably be generally achieved by A. Create appropriate temp table(s) B. Populate table(s) from Oracle. The design of the temp tables depends specifically on business information based on the what you are doing in SSRS and how that relates to the data in Oracle. So if you cannot determine the structure of the tables yourself you are going to need to explain the problem in detail or provide a sample that has been reduced from those requirements. Keep in mind that you do NOT attempt step 2 until you have completed step 1.

S Douglas

Shameel wrote: AFAIK, SQL Server does not support packages, only Oracle does FYI, SSIS (SQL Server Intergration Services) does use the term 'packages' for its code base.

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.