Re: Isolation of code in SPL

ASC wrote: > > Hello, > > I have written a procedure that needs to create a temp table. > > ... do something > select ... into temp tablename > ... do something > drop table tablename> ... > The table is dropped when I finish to use it. The problem is that the procedure > can be executed by several users at the same time and the table is the same for > all. So the table can't be created while another user is using it, and haven't > dropped it yet. > Can isolate this parts of code with set isolation or set transaction? I think > these statements work to isolate access to table rows, but I need to isolate > code (and so the entire temp table). Anybody can explain to me a solution? > I am using Online Dynamic Server 7.2 Temp tables are guaranteed unique for each session. If you are getting "TABLE ALREADY EXISTS" errors (-310) when a user executes the procedure several times it is because you are selecting from the temp table in a FOREACH loop with a RETURN...WITH RESUME and dropping the temp table after the loop. This looks good on paper, but, if you do not fetch all rows the loop never exits so the drop table is not performed. Just move the drop table to the beginning of the procedure and place an exception to ignore the "TABLE NOT FOUND" error (-206) that will result the first time the procedure is run. It is tempting to catch the -310 error and drop the table in the exception with a "WITH RESUME" clause but it will not work as many have found out. The exception will return after the select that creates the temp table and the rest of the procedure will bomb or get old data. Art S. Kagel