Re: Stored Procedures

At 08:24 06/15/2001, Jose Rodriguez wrote:
>I've got a simple question.  I'd like to be able to code a stored procudrure
>in SQL that creates a temporary table.  The idea is that I need a unique
>number to add to the end of the table name (e.g. TEMP12345).  This way if
>two instances of the stored procedure are running, they don't bump into one
>another.
>
>Does anyone know of a random function or of a way to access a unique number
>within the stored procedure for this purpose?
>
>The only idea I have come up with is a table that holds the number (similar
>to a data area, but in SQL).

If it's truly a temporary table, you can use a literal name and create the 
object in the QTEMP library. QTEMP is unique for each job, so there will be 
no name conflict, as long as the stored procedure is not executed 
simultaneously in multiple threads within the same job (I'm don't think you 
can do that, but it would certainly cause a problem if you did). I've used 
this method when I've needed to get data from several places and return a 
sequenced result cursor. It works fine. Just be sure to drop the table 
before you create it, just in case it already exists in QTEMP, which can 
happen if you execute the procedure more than once within the same job.

Pete Hall
pbhall@execpc.com
http://www.execpc.com/~pbhall/

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---