Memory Leak in Stored Procedure ?

[saravanan santhanam]

Hi,

We are having a Procedure with an XML data as input .

Let us call it as PROC_A

Proc_A from its XML input , extracts process_id.

With this Process_id, it calls another Procedure PROC_B

Proc_b with this process_id looks for records. if there are more records it then calls another procedure PROC_C if it finds only one record it returns that record and comes out.

Proc_C selects those records makes some calculations and returns the records

so PROC_A calls PROC_B. PROC_B might call PROC_C

When we give input to PROC_A in the XML we give id1,id2, id3

PROC_B takes id1 and finds that id1 2 record then it calls PROC_C

PROC_C after its calculations returns 2 records

Control now back to PROC_A

PROC_A calls PROC_B with id2

PROC_B takes id2 and finds that id1 only 1 record PROC_B returns that record and

Control now back to PROC_A

PROC_A calls PROC_B with id3

PROC_B takes id3 and finds that id3 2 record then it calls PROC_C

Now comes the real issue

PROC_C has to return the records of id3 but it returns the records of id1

We tried displaying what the value of the id travelling from PROC_A , PROC_B, PROC_C

while every where the data is correct but when proc_c returns it returns id1 records instead of id3

then we tested with 3 ids with id1 and id 2 which has 1 record each then id3 which has 3 records
this time we had all correct records

but if we have id1 - 1 record , id2 - 2 records and id3- 3 records then instead of id3 we get id2

we are baffled !

is there any specific reasons with mysql or we missing some thing