1. what is the purpose of using temp. tables inside stored procs?
2. When the temp. table will get destroyed if it is not dropped in a stored pro?
Also tell me whether (a) or (b) is more efficient.
(a) I have a single query with two Full outer joins declared in a cursor. I'll open it at the end of the proc for the fron end to retrieve data.
(b) I have three cursors... I'll declare a temp table and populate each row with the value fetched from the three cursor and insert it to the temp table. Finally I'll declare a cursor for that temp table to fetch all rows and open it at the end.
(a) is more efficient it has lesser overheads.
All though, being it an outer join, (a cartisian product), you should think of optimization with putting restriction as opposed to join before restriction before join if possible.