Multiple select and update statements in a single stored procedure

Question

Hi All, 
 I have one requirement where i have to update 3 tables (i need to make some rows as inactive based on the conditions i have) where as these 3 tables are inter related and i have to select primary key form table 1 to update table 2 , and if need to update table 3 i have to select PK of table 2 in turn i have to select PK of table 1 
 so for this i have written single mysql stored procedure with three update statements in one update statement two selection in turn , in one update statement one selection(to select relation) , its working fine but my question here is is this approach to write stored procedure is fine ? 
 I can't assign the relation to one variable because it might return multiple values where that is not supported in stored procedure. 
 
 Thanks, 
 Bhargavi P

aloks0189 · Answer

Hi bhargavip0001 If i understood your Statement correctly, the actual problem lies in, how to store the multiple values in Stored Procedure. 
 
If so, may i know which database are you working with? e.g. MySQL or Oracle? 
 
If you are working with MySQL database then i would recommend you to go with Temporary Table concept, where first you need to create a temporary table, then set the data into this table and use this temporary table instead of Array Variables and make sure to drop it once you are done. 
 
NOTE: A Temporary table is visible only within the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non- TEMPORARY table of the same name. However to be in safer side, try deleting the Temporary Table once you are done with the processing. 
 
But If you are working with Oracle, then you can define VARRAY to hold multiple values in it as follows: 
 TYPE varray_type_txt IS VARRAY(10000) OF varchar2(1000 char); 
 
And make the use of this variable to perform the set of operations in your procedure. 
 
Hope this will help you.